Whenever, I had to reverse text in Excel, I used to create an user defined function using STRREVERSE function in VBA. But after when I started using Power Query, I found an alternative to the VBA method.
Let’s see how to reverse text strings in Excel using Power Query.
Following is the list of Names which I want to reverse.
To load this data into Power Query Editor,
Select a cell in the list > go to the Data Tab > Click on From Table/Range
I have renamed this Query as Names using the Task Pane on right side of the Power Query Editor.
Now, to create a column containing the reversed texts,
Add Column Tab > Custom Column
In the Custom Column dialog box, change the New Column Name to Reversed Text [optional] and use the following formula
=Text.Reverse([Names])
Text.Reverse is the M function to reverse text and Names is the column containing text to be reversed.
When you click OK, a new column called Reversed Text will be created that contains the reverse of the text strings present in the column, Names.
To remove the first column called Names, Right-click on the column header and select Remove.
Now, we have only one column which contains the reversed strings.
To load this data into the worksheet, click on Close & Load in the Home tab > Close & Load to
Import Data dialog, Select Existing worksheet > Select the cell where you want to place the data (here I have selected the cell D2) > Click OK
In the column D we have the Reverse of the Texts in Column B
As you can see here, the texts are all reversed as it is and the last letter of each reversed text is in Upper Case. If you want to Capitalize the first letters of every reversed text, we can make use another M function, Text.Proper
To edit the query called Names, Right-click on the query and select Edit
On the right sidebar, Double-click on the step called Added Column
Again we have the dialog box called Custom Column
In the dialog, use the following formula and click OK
=Text.Proper(Text.Reverse([Names]))
Text.Proper is the M function to convert text strings into Proper Case.
Click on the Close & Load button in Home tab and the output table in the worksheet will be updated like the following
As you may know, one of the great features of Power Query is that you can update the output table simply by clicking on the Refresh button in the Data tab.
So, let’s add some data into the source table > Right-click on the output table > Refresh
