How to loop through each character within a string

You've worked with text data before, used formulas, and created reports, but how do you up your game? Efficiently loop through text data using this simple VBA code to analyze or manipulate strings.

Code

Sub LoopThroughString()
Dim counter As Integer
Dim mystring As String
mystring = "Excel is good" 'define string
For Counter = 1 To Len(mystring) 'count from 1 to length of string
    Debug.Print Mid(mystring, Counter, 1) 'do something to each character in string
Next
End Sub

How

First, we need to define the variables. Integer counter, string mystring, and set mystring a text value. Here we used a static text value here, but you can set dynamic values to cells by using the RANGE() function. For example,

mystring = Range("A1").Value.

Next, we need to use a For-Next Loop to iterate each character in mystring. Counter variable gets an incremental value for each character, from 1 to the length of the string variable (i.e. Len(mystring)). On every step of the For-Next Loop, a character in mystring will be caught by the Mid function. Mid works in same way as the MID formula, it returns a string part from a source parameter. Counter indicates the number of characters and 1 is the length of the string to grab.

Here, we used Debug.Print method for demonstration purposes. This argument prints the strings into an Immediate window.