0

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.

  • My strings are similar to "John|XYZ Street|215|USA" and I need to find the text after LAST "|" character. So, I thought about revert the string and locate the text before the FIRST "I" in that. If anyone knows any other way to find the LAST "|", it can be a useful solution. Note 1: the number of "|"s varies from string to string; so, search 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. – Marcelo Aug 12 at 13:40
  • 1
    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 at 13:50
  • 1
    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 at 5:32
  • Possible duplicate of Any Excel function that will reverse a string? – fixer1234 Aug 13 at 7: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 at 7:15
3

Here is an option:

enter image description here

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.

  • 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 S Aug 13 at 6: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 at 7:05
  • it's the big mess,, OP has twisted the question without modify original post,,!! – Rajesh S Aug 13 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.