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

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.

Option Explicit

Sub splitArray()
    Dim str As String
    str = "Hello, I am Arun Banik"      ' the string that will split.
    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.

← PreviousNext →