Following are 3 different methods to reverse text strings in Excel.
Table of Contents
Dynamic Array Formula to reverse Text
The following formula combines the MID, TEXTJOIN, LEN and the SEQUENCE function to reverse the text string supplied into it.
=TEXTJOIN("",1,MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1))
Let’s see what happens when we supply the text ‘Hana’ into the above formula.
=TEXTJOIN("",1,MID("Hana",SEQUENCE(LEN("Hana"),,LEN("Hana"),-1),1))
Length of the text string ‘Hana’ is 4. LEN(“Hana”) will return 4.
=TEXTJOIN("",1,MID("Hana",SEQUENCE(4,,4,-1),1))
SEQUENCE(4,,4,-1) will generate a vertical array of numbers from 1 to 4.
=TEXTJOIN("",1,MID("Hana",{4;3;2;1},1))
MID(“Hana”,{4;3;2;1},1)) will result in a vertical array like this,{“a”;”n”;”a”;”H”}
=TEXTJOIN("",1,{"a";"n";"a";"H"})
Finally, the TEXTJOIN function will combine the letters in the array will return ‘anaH’.
="anaH"
VBA to Reverse Text
The StrReverse function in VBA will reverse the text string supplied into it. We can create a User Defined Function (UDF) using as well as Macros using this VBA function, to reverse strings.
User Defined Function to reverse text
Copy and paste the following code into the VBA editor of Excel to create an UDF called ReverseString.
Function ReverseString(text As String) ReverseString = StrReverse(text) End Function
Type in the first few letters of ReverseString function and the same will appear in the list of suggested functions.
Text strings reversed using the newly created ReverseString function.
Macro to reverse Text strings in the selected cells.
Sub ReverseStringsInASelection()
Dim X As Range
Dim Y As Integer
Y = 0
Set X = Selection
For Each X In Selection
ActiveCell.Offset(Y, 1).Value = StrReverse(X)
Y = Y + 1
Next X
End Sub
Select the cells containing Text Strings, Execute the Macro ReverseStringsInASelection and adjacent cells will be populated with reversed text strings.
Power Query to reverse Text
Text.Reverse is a Power Query M function which will reverse the text string supplied into it.
The following formula can be used to reverse the text strings in the table called Names.
=Text.Reverse([Names])
Read about Reversing Text string using Power Query
