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