How to reverse a string in Excel?

Question

Is there an Excel function (or macro) I can use to reverse a string. i.e. to turn these words into sdrow eseht?

Here is why I need that:

My strings are similar to John|XYZ Street|215|USA and I need to find the text after LAST | character.

So, I thought about reversing the string to locate the text before the FIRST | in that. If anyone knows any other way to find the LAST |, it can be a useful solution.

Note 1: the number of | varies from string to string; so, searching for the 3rd | will not work.

Note 2: I know the text-to-columns button, but I can't use it, since it would affect all the structure of my sheet.

JvdV · Accepted Answer · 2019-08-12T13:58:08.570

Here is an option:

Formula in B1:

=TRIM(RIGHT(SUBSTITUTE(A1,"|",REPT(" ",LEN(A1))),LEN(A1)))

It's basically a small adaptation from this source's explanation on how to find a cell's "last word". I just swapped the spaces in a normal sentence with a pipe symbol in your case.

Of topic since you techniqually are not asking for it, but to reverse a text string, you could look into that same website's explanation on how to do that.

mark fitzpatrick · Answer 2 · 2021-05-12T17:03:45.067

You can use this LET formula:

=LET( string, D6,
       L, LEN( string ),
       CONCAT( MID( string, SEQUENCE( 1, L, L, -1), 1) ) )

It breaks up the string into an array of 1-character cells and then recombines them back into a concatenated string in reverse order.

Having read the full problem statement, here is an approach that will address this issue. It does not rely on reversing the string, but I can see why you would do it that way.

First, let's make a string splitter:

=LET( string, A1,
       delimiter, A2,
       IF( ISBLANK( string ), "",
             IF( LEN( delimiter ) = 0,
                   MID( string, SEQUENCE( 1, LEN( string ) ), 1),
                   TRANSPOSE( FILTERXML( "<main><sub>" & SUBSTITUTE( string, delimiter, "</sub><sub>" ) & "</sub></main>", "//sub" ) ) ) )
     )

This just splits the cell contents into an array of values according to a delimiter. Now you can modify this get the last element of the array. Here is one way to do that:

=LET( string, A1,
       delimiter, A2,
       strArray, IF( ISBLANK( string ), "",
                             IF( LEN( delimiter ) = 0,
                                  MID( string, SEQUENCE( 1, LEN( string ) ), 1),
                                  TRANSPOSE( FILTERXML( "<main><sub>" & SUBSTITUTE( string, delimiter, "</sub><sub>" ) & "</sub></main>", "//sub" ) ) ) ),
       elements, COUNTA( strArray ),
       INDEX( strArray, 1, elements )
     )

It is a little heavy, but it is also versatile.

score 0 · Answer 3 · edited May 12 '21 at 13:42

A1 blank, A2 the string to be reversed, B1 zero, and a formula in B2 which is a chain of the form =IFERROR(MID($A2,25+B$1*25,1)&IFERROR(MID($A2,24+B$1*25,1)&..... and so on, with the term ‘25+’ reducing as shown step by step all the way down to 1.

With B$1 containing 0, this will reverse any string up to 25 characters long.

To scale this for longer strings, 100 characters, say, copy the formula to C2:E2, with C1:E1 being 1,2 and 3.

Then in F2, have =E2&D2&C2&B2, which will then hold the reversal of the entire string.

Given enough columns, or doing something very similar with rows instead of columns, you could in principle reverse War And Peace, if you could get it into one cell to begin with.

But the main things are that it scales, and you don’t need to know how long the string is exactly; the one column version will handle 25, 10, or even zero characters without erroring.

And yes, I know there’s a VBA function to do this; but where I work, Mordac, the Preventer of Information Technology, is alive and well and does not like .xlsms

How to reverse a string in Excel?

3 Answers3