How To Reverse a Text String in ExcelUsing Formula & VBA

Written By:
Lisa Borga
Reviewed By:
FundsNet Staff

There is not a large need for reversing a text string, particularly when using Excel or other spreadsheet programs.

So, there is no built-in function for doing this in Excel.

However, you may actually have a need to reverse a text string at some time.

It’s possible you may need to correct a string that was flipped when it was imported in Excel, or maybe you need to generate a special code.

Although there are no built-in functions in Excel that you can use to reverse text strings in Excel, there are a couple of ways you can do this, a formula or a script, and we will explain these methods in this article.

How To Reverse a Text String with Text Formulas

Using the MID and CONCATENATE functions, it is possible to flip a string of text in reverse.

For example, to reverse the text Apple in cell C2 and enter it into cell D2, enter the following formula:

=MID($C$2,LEN($C$2)-ROW(D2)+2,1).

If performed correctly, this will return the last letter in Apple, e, in cell D2.

Now select the fill handle on the bottom-right corner of the cell and drag it down to reveal the rest of the word in reverse.

Simply drag down until you see “#VALUE!”.

With all of the words in reverse order, now we enter “=TRANSPOSE(” in the cell underneath the final cell in the column with our reversed text string and drag and select all of the cells in our column.

Now the closing parentheses are entered in the TRANSPOSE formula, and we press “F9” on our keyboard.

This will display all of the letters in our column, separated by commas and enclosed with curly brackets.

We can now remove the curly brackets and enter “CONCATENATE=(“ after the last letter in the list, add the closing parentheses and select “Enter.”

This will now display the reverse of the word. In our case, this now displays elppA in cell D8.

In order to use this formula on your own text, simply replace the cell references with the appropriate references in your own document.

How To Reverse a Text String Using VBA

As with most tasks in Excel, we can get the job done using a VBA macro. Though this probably sounds quite intimidating, it really is not as hard as it sounds, and once you are familiar with this method, it is one of the easiest ways to reverse a text string.

Here is how.

  1. Open the VBA editor by navigating to the “Developer” tab and selecting “Visual Basic.” If you haven’t added the “Developer” tab yet, you can do so by selecting “File,” “Options,” and “Customize Ribbon.” Here you can select “Main Tabs” from the “Choose commands from” list and select to add the “Developer” tab.
  2. When the VBA window appears, select “Insert” and “Module.”
  3. In the module window, enter the following code:
Sub Reverse_String()

Dim s As Range

Set s = Application.Selection

s.Offset(0, 1).Value = StrReverse(s)

End Sub
  1. You can run the code by clicking “F5” on your keyboard.
  2. You can select a cell containing the text you would like to reverse. Make certain that the ensure cell beside the one to be reversed is blank because this is where it will be displayed.
  3. You can apply the Macro by navigating to the “Developer” tab and selecting “Macros” from the “Code” group. In the “Macros” dialog box, you can select “Reverse_String” and then “Run.”

Your reversed text will now be displayed in the output cell next to the original text.

How To Reverse Text Strings with the TEXTJOIN Formula

Another method that can work excellently for text strings is the TEXTJOIN formula.

For text of ten characters or less, this is as easy as entering the following formula into a blank cell with your cell references substituted into the formula and selecting enter.

=TEXTJOIN(“”,1,MID(Cell Reference,{10,9,8,7,6,5,4,3,2,1},1)

The method above is possibly the easiest method for reversing a text string; however, in many cases, ten characters may not be enough to handle your text string.

In this case, the following somewhat more complicated formula can be used in its stead.

=TEXTJOIN(“”,1,MID(Cell Reference,,ABS(ROW(INDIRECT(“1:”&LEN(Cell Reference)))-(LEN(Cell Reference)+1)),1))

Simply input the appropriate cell reference for the text string you wish to reverse and enter the formula into an empty cell.

Conclusion

Text strings are often used in Excel, and when they must be reversed for any reason, such as adjusting formulas, performing the task manually can be arduous.

This is why tools such as those above can be extremely valuable for quickly and efficiently working with text strings.

Any of the methods we have covered can get the job done, and you can use whichever you feel most comfortable with.