How do I reverse individual words in a string in VBA?

Question

I've managed to reverse the string as a whole using the code below:

Dim str As Variant
Dim i As Integer, strLen As Integer
Dim NewStr As String


str = "VBA is amazing"

strLen = Len(str)
NewStr = ""
 
For i = 1 To strLen
  NewStr = NewStr & Mid(str, strLen - (i - 1), 1)
Next i

MsgBox (NewStr)

The outcome is: gnizama si ABV, but I want it to be: ABV si gnizama

Goal is not to use Split or StrReverse.

Answer 1

Try,

Sub test()
    Dim strL As Variant
    Dim i As Integer, strLen As Integer
    Dim NewStr As String
    Dim s As String, s1 As String
    
    strL = "VBA is amazing"
    strLen = Len(strL)
    NewStr = ""
     
    For i = 1 To strLen
        s = Mid(strL, i, 1)
        If s = " " Then
            NewStr = NewStr & s1 & s
            s1 = ""
        Else
            s1 = s & s1
        End If
    Next i
    NewStr = NewStr & s1
    MsgBox (NewStr)
End Sub

Answer 2

Not sure why you don't want to use Split and StrReverse. The implementation is easy:

Sub Test()
    Dim s As String
    Dim words() As String
    Dim i As Long
    Dim word As Variant
    
    s = "VBA is amazing"
    
    words = Split(s, " ")
    
    i = LBound(words, 1)
    For Each word In words
        words(i) = StrReverse(word)
        i = i + 1
    Next word

    MsgBox Join(words, " ")
End Sub

If, for whatever reason, you cannot use Split or StrReverse then you need something like this:

Sub Test2()
    Const SPACE As String = " "
    Dim lastSpace As Long
    Dim nextSpace As Long
    Dim s As String
    Dim word As String
    Dim newStr As String
    
    s = "VBA is amazing"
    
    nextSpace = InStr(1, s, SPACE)
    If nextSpace = 0 Then
        newStr = ReverseWord(s)
    Else
        Do While nextSpace > 0
            word = Mid$(s, lastSpace + 1, nextSpace - lastSpace - 1)
            newStr = newStr & ReverseWord(word) & SPACE
            lastSpace = nextSpace
            nextSpace = InStr(nextSpace + 1, s, SPACE)
        Loop
        word = Right$(s, Len(s) - lastSpace)
        newStr = newStr & ReverseWord(word)
    End If

    MsgBox newStr
End Sub

Private Function ReverseWord(ByVal word As String) As String
    Dim i As Long
    Dim res As String
    
    For i = Len(word) To 1 Step -1
        res = res & Mid$(word, i, 1)
    Next i
    ReverseWord = res
End Function

EDIT #1

The ReverseWord method can be optimized:

Private Function ReverseWord(ByVal word As String) As String
    Dim i1 As Long: i1 = 1
    Dim i2 As Long: i2 = Len(word)
    Dim c As String
    
    Do While i1 < i2
        c = Mid$(word, i1, 1)
        Mid$(word, i1, 1) = Mid$(word, i2, 1)
        Mid$(word, i2, 1) = c
        i1 = i1 + 1
        i2 = i2 - 1
    Loop
    ReverseWord = word
End Function

Answer 3

other solution from me

Sub test()
    Dim str As Variant
    Dim i As Integer, strLen As Integer, iSp As Integer, n As Integer, f As Integer
    Dim NewStr As String
    Dim wordPart() As String
    f = 0
    n = 1
    str = "VBA is amazing"
    strLen = Len(str)
    NewStr = ""
    iSp = spacesCounter(str) + 1
    ReDim wordPart(iSp)
    
    For i = 1 To strLen
        If Mid(str, i, 1) = " " Then
            If n = 1 Then
                wordPart(n) = Mid(str, 1, i - 1)
                f = i + 1
                n = n + 1
            Else
                wordPart(n) = Mid(str, f, (i) - f)
                f = i + 1
                n = n + 1
            End If
        ElseIf i = strLen Then
            wordPart(n) = Mid(str, f)
        End If
    Next i
    
    For i = 1 To iSp
        MsgBox (wordPart(i))
        For j = 1 To Len(wordPart(i))
            If i <> 1 And j = 1 Then NewStr = NewStr & " "
            NewStr = NewStr & Mid(wordPart(i), Len(wordPart(i)) - (j - 1), 1)
        Next j
    Next i
    
    MsgBox (NewStr)
    
    
End Sub

Answer 4

Try this:

Function CustomReverse(inputString As String) As String
    Dim wordSeparator As String, reversedString As String
    Dim i As Integer, j As Integer, k As Integer, vector As Integer
    reversedString = inputString
    wordSeparator = " "
    
    i = 0
    j = 1
    Do
        i = InStr(i + 1, inputString, wordSeparator, vbTextCompare)
        If i = 0 Then i = Len(inputString) + 1
        For k = j To i - 1
            If Mid(reversedString, k, 1) = wordSeparator Then Exit For
            vector = i - k - 1
            Mid(reversedString, k, 1) = Mid(inputString, j + vector, 1)
        Next
        j = i + 1
    Loop While i > 0 And j < Len(inputString)
    
    CustomReverse = reversedString

End Function

Usage:

Sub Test()
    Debug.Print CustomReverse("VBA is amazing")
    'prints: "ABV si gnizama"
End Sub

Answer 5

Solution without Split or StrReverse:

Sub RevWords(Optional str As String = "VBA is amazing")
    ' RegExp: need to switch on «Microsoft VBScript Regular Expression 5.5» in menu «Tools/References»
    Dim RegEx As New RegExp
    Dim res As Object, revStr As String, i As Integer, s As Variant
    
    With RegEx
        .Global = True
        .Pattern = "([^ ]+)"
        Set res = .Execute(str)
    End With
    
    For Each s In res
        e = Len(s)
        Do While e > 0
            revStr = revStr & Mid(s, e, 1)
            e = e - 1
        Loop
        revStr = revStr & " "
    Next
    
    Debug.Print str & " >> " & Trim(revStr)
End Sub

' output: VBA is amazing >> ABV si gnizama

Answer 6

Goal is not to use Split or StrReverse.

But those are what to use:

    Dim Straight    As String
    Dim Reversed    As String
    Dim Index       As Long
    Dim Words()     As String
    
    Straight = "VBA is amazing"
    Words = Split(Straight)
    
    For Index = LBound(Words) To UBound(Words)
        Words(Index) = StrReverse(Words(Index))
    Next
    
    Reversed = Join(Words)
    Debug.Print Reversed

    ' ABV si gnizama

If not, you must either roll your own substitutes or run a double loop indentifying spaces and, between these, collect and reverse the words. Slow, but doable.