Reverse sentence order excel

Question

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

Answer 1

If you have later versions of Excel (tested with Office 365) that support TEXTJOIN(), TEXTSPLIT(), SEQUENCE() functions, then below will do the job with no VBA or Lambda.

=TEXTJOIN(" ",FALSE,SORTBY(TRANSPOSE(TEXTSPLIT(A1," ")),SEQUENCE(COLUMNS(TEXTSPLIT(A1," ")),1,COLUMNS(TEXTSPLIT(A1," ")),-1)))

Reference/Credit: This answer was inspired by https://stackoverflow.com/a/70771028/7270462

Answer 2

With Excel 365, you may try:

Formula in B1:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"),TEXTJOIN(" ",,SORTBY(X,SEQUENCE(COUNTA(X)),-1)))

For those without LET(), you can try using Excel 2019:

=TEXTJOIN(" ",,INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"),N(IF(1,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+2-ROW(A1:INDEX(A:A,(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))+1))))))

Obviously, this would need you to CSE-enter the formula.

---

EDIT 30-3-2022:

As per the newest functions in ms365, we could try:

=REDUCE("",TEXTSPLIT(A1," "),LAMBDA(a,b,TEXTJOIN(" ",,b,a)))

Or:

=TRIM(REDUCE("",TEXTSPLIT(A1," "),LAMBDA(a,b,b&" "&a)))

Answer 3

Version 365

Just for fun an alternative via Sequence() function (version 365):

Function getReverse2(sentence As String) As String
    If sentence = vbNullString Then Exit Function
    'a) split string into tokens
    Dim tokens:   tokens = Split(sentence)
    'b) build decreasing series
    Dim newOrder: newOrder = Application.Sequence(1, UBound(tokens) + 1, UBound(tokens) + 1, -1)
    'c) rearrange tokens in descending column order
    getReverse2 = Join(Application.Index(tokens, 0, newOrder))
End Function

---

Edit #1 Backwards compatible // as of 2021-01-14

Not as short as the original approach above, but replying to @FaneDuru 's comment to provide also for a backwards compatible approach:

Function getReverse(sentence As String) As String
    If sentence = vbNullString Then Exit Function
    'a) split string into tokens
    Dim tokens:   tokens = Split(sentence)
    'b) build decreasing series
    Dim neworder: neworder = getNewOrder(tokens)         ' << added function call
    'c) rearrange tokens in descending column order
    getReverse = Join(Application.Index(tokens, 0, neworder))
End Function

Help function getNewOrder()

Function getNewOrder(arr, Optional ByVal Is365 = False)
    '' //version 365:
    '    getNewOrder = Application.Sequence(1, UBound(tokens) + 1, UBound(tokens) + 1, -1)
    
    '' //prior versions    '
    Dim cols As Long: cols = UBound(arr) + 1: ReDim tmp(1 To cols)
    Dim col As Long:  For col = 1 To cols: tmp(col) = cols - col + 1: Next col
    getNewOrder = tmp
End Function

---

►Edit #2 Backwards compatible - Refined // as of 2021-11-12

Instead of looping to imitate a reverse Sequence (vers. MS 365) I found a workaround via evaluation based on column numbers:

newOrder = Evaluate(i & "-Column(A:" & Split(Cells(1, i).Address, "$")(1) & ")+1")

where Split(Cells(1, i).Address, "$")(1) simply gets the column name of the starting element number i.

Function getReverse2(sentence As String) As String
    If sentence = vbNullString Then Exit Function
    'a) split string into tokens
    Dim tokens:   tokens = Split(sentence)
    Dim i As Long: i = UBound(tokens) + 1     ' number of splitted elements
    'b) build decreasing series
    'Dim newOrder: newOrder = Application.Sequence(1, UBound(tokens) + 1, UBound(tokens) + 1, -1)
    Dim newOrder
    newOrder = Evaluate(i & "-Column(A:" & Split(Cells(1, i).Address, "$")(1) & ")+1")
    'c) rearrange tokens in descending column order
    getReverse2 = Join(Application.Index(tokens, 0, newOrder))
End Function

Answer 4

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

Answer 5

Try the next simple function, please:

Function revWordsInSentance(x As String) As String
  Dim arr, arrRev, i As Long

  arr = Split(x, " "): ReDim arrRev(UBound(arr))
  For i = 0 To UBound(arr): arrRev(i) = arr(UBound(arr) - i): Next i
    
  revWordsInSentance = Join(arrRev, " ")
End Function

It can be tested using the next way:

Sub testRevWordsInSent()
  MsgBox revWordsInSentance("Hello my name is")
End Sub