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.
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.