[SOLVED] Reverse sentence order excel

Reverse sentence order excel

I can only find functions which reverses the words, e.g. Hello -> olleH. However, I need a function which reverses the order of a sentence from last to first. An example could be: "Hello my name is" -> "is name my Hello".

Any suggestions either in VBA or in prebuilt functions?

Thanks

Solution

Great thanks, the best would be a function which I can control within other functions... – emilk 36 mins ago

Here is an alternative where you can pass the range which has the value.

Option Explicit

Sub Sample()
    MsgBox ReverseString(Range("A1"))
End Sub
    
Private Function ReverseString(rng As Range) As String
    On Error GoTo Whoa
    
    Dim MyAr As Variant, itm As Variant
    
    MyAr = Split(rng.Value2, " ")
    
    Dim arListCollection As Object
    
    Set arListCollection = CreateObject("System.Collections.ArrayList")
    
    With arListCollection
        For Each itm In MyAr: .Add itm: Next itm
        .Reverse
        MyAr = .Toarray
    End With
    
    ReverseString = Join(MyAr, " ")
LetsContinue:
    Exit Function
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Function

If you want to pass a string to the function, for example

Sub Sample()
    MsgBox ReverseString("Hello my name is")
End Sub

Then the function becomes

Private Function ReverseString(s As String) As String
    On Error GoTo Whoa
    
    Dim MyAr As Variant, itm As Variant
    
    MyAr = Split(s, " ")
    
    Dim arListCollection As Object
    
    Set arListCollection = CreateObject("System.Collections.ArrayList")
    
    With arListCollection
        For Each itm In MyAr: .Add itm: Next itm
        .Reverse
        MyAr = .Toarray
    End With
    
    ReverseString = Join(MyAr, " ")
LetsContinue:
    Exit Function
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Function

Interesting Read: Quick Guide to the VBA ArrayList