Read every character in a string using excel VBA

This example reads every character in a string from left to right and returns the result in a message box. It makes use of the Mid function.

Sub LoopThroughString()

Dim LookInHere As String
Dim Counter As Integer

'Use your own text here
LookInHere = "AutomateExcel.com"

For Counter = 1 To Len(LookInHere)
    MsgBox Mid(LookInHere, Counter, 1)
Next

End Sub

Read every word in a string using excel VBA

This example reads every word in a string from left to right and returns the result in a message box. It makes use of the Split function.

Sub LoopThroughString2()

Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant

'Use your own text here
LookInHere = "I Heart AutomateExcel.com"

SplitCatcher = Split(LookInHere, " ")

For Counter = 0 To UBound(SplitCatcher)
    MsgBox SplitCatcher(Counter)
Next

End Sub