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.

Quite some relevant information you got there in this comment. I suggest you edit your question to include all of that for future reference and to enhance the quality of your question. — JvdV, Aug 12 '19 at 13:50
Your actual requirement is hiding in a transient comment. You're asking something totally different as a way to eventually get to what you need (after applying more formulas to unravel it). What's in the question is a bad approach, but what you explain in your comment is very doable, several different ways. JvdV's answer may attract downvotes, even though it solves your problem, because it answers the comment but is totally unrelated to the question. Please do yourself and everyone who may try to answer a favor, and rewrite the question to contain essentially what's in the comment. — fixer1234, Aug 13 '19 at 05:32
Possible duplicate of [Any Excel function that will reverse a string?](https://superuser.com/questions/121618/any-excel-function-that-will-reverse-a-string) — fixer1234, Aug 13 '19 at 07:12
What you ask in the question has already been asked and answered (see proposed duplicate), so the question is likely to be closed as-is. That would be another reason to put your actual problem in the question. — fixer1234, Aug 13 '19 at 07:15
@fixer1234,, this edited version is almost similar,, which creates lots of confusion for the readers,, since OP demands to crack two different issues !! Now,, which answer should be appreciated,, since I've solved the first part & JvdV solves the second one? — Rajesh Sinha, Aug 13 '19 at 07:32
@Marcelo,, better you decide that which one is the core issue, since both are totally different,, better pick one you need or [Edit] your post & write that you need both to solve,, if you need so !! — Rajesh Sinha, Aug 13 '19 at 07:36
@RajeshS, It's even worse than you realized. The original question is a dupe. If you go there, that already has your answer. In fact, Gangula posted it here before you and then deleted his answer. — fixer1234, Aug 13 '19 at 07:40
Thank very much to all comments and to JvdV solution. And sorry for the unwanted confusion (I searched for "reverse string in Excel" but didn't found that other topic).. — Marcelo, Aug 13 '19 at 14:11

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

3

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.

edited Aug 12 '19 at 13:58

answered Aug 12 '19 at 13:48

JvdV

2,217
6
13

OP is expecting to reverse the order of the text string like the link shows `explanation`,, but your answer is different !! I think you need to correct it. – Rajesh Sinha Aug 13 '19 at 06:19
@rajeshS, thanks for the comment, but I based my answer purely on further explanation by OP in his comments below the original question. – JvdV Aug 13 '19 at 07:05
it's the big mess,, OP has twisted the question without modify original post,,!! – Rajesh Sinha Aug 13 '19 at 07:13

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.

I cam here for an answer to the title and am glad someone wrote a formula-based answer to it. Thanks. — Engineer Toast, May 12 '21 at 13:26
Crazy timing. I just posted it. Glad it helped. The original problem would have a different answer - I could post that too, but I think it may be asked/answered elsewhere. Will see. — mark fitzpatrick, May 12 '21 at 16:07

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