How to Split a String into an Array of Characters in Excel using VBA

← Prev

You can use the Mid() function in VBA to split a given string into an Array of characters in Excel.

The string can be in a variable or extracted from a cell in your worksheet. You can easily split the string into an array of characters in VBA. The method is simple.

The Macro
Option Explicit

Sub splitArray()
    Dim str As String
    str = "Hello, I am Arun Banik"
    Dim arr() As String
    ReDim arr(Len(str))
    
      
    Dim iCnt As Integer
    For iCnt = 1 To UBound(arr)
        arr(iCnt) = Mid(str, iCnt, 1)		' Split the string. 
    Next iCnt
    
    ' Now use the characters stored in the Array.
    Dim x, y  As Integer
    y = 1
    For x = 1 To UBound(arr)
        If x >= 12 Then
            Worksheets("sheet2").Cells(x, x - y) = arr(x)
            y = y + 2
        Else
            Worksheets("sheet2").Cells(x, x + 1) = arr(x)
        End If
        If arr(x) = "B" Then
            Worksheets("sheet2").Cells(x, x - (y - 2)).Font.Color = vbRed
        End If
    Next x
End Sub

I have a string variable, which I want to split into an array of characters. So, I have declared an array “arr()” of type string, Redim the array, that is assign a size to array.

Dim arr() As String
ReDim arr(Len(str))

Next, run a loop an extract each character in the string using the “Mid()” function and store the characters in the array, that you declared.

arr(iCnt) = Mid(str, iCnt, 1)

The Mid() function returns a specified number of characters. We want it to return a single character in the loop. It takes three parameters.

1) String - the string value from which a specified number of character(s) is returned.

2) Start - the starting position in the string: its where it starts extracting the characters.

3) Len or Length – the number of characters this function would return.

That’s it.

You can now read the characters by simply looping through the array. Use the characters as you want. I am writing the characters one by one in my worksheet.

Thanks for reading.

← Previous