How can I perform a reverse string search in Excel without using VBA?

Question

I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different.

Using built in Excel functions (no VBA), is there a way to isolate the last word in each string?

Examples:

  Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
                  Aziz! Light! -> Light!

Answer 1

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

A quick search found this.

Answer 2

It's hard to answer your question properly if you don't indicate what makes VBA inappropriate (since you can write your own macros and functions in VBA, making it equivalent to the built-in functions).

Answer 3

This is ugly but it sort of works. I am assuming you have the string in A1. You need an intermediate cell (well, not really, but it makes it a bit easier to read). In the intermediate cell (B1) put this formula:

=IF(NOT(ISERROR(SEARCH(" ",A1,LEN(A1)-1))),SEARCH(" ",A1,LEN(A1)-1),
  IF(NOT(ISERROR(SEARCH(" ",A1,LEN(A1)-2))),SEARCH(" ",A1,LEN(A1)-2),
   IF(NOT(ISERROR(SEARCH(" ",A1,LEN(A1)-3))),SEARCH(" ",A1,LEN(A1)-3),
    IF(NOT(ISERROR(SEARCH(" ",A1,LEN(A1)-4))),SEARCH(" ",A1,LEN(A1)-4),
     IF(NOT(ISERROR(SEARCH(" ",A1,LEN(A1)-5))),SEARCH(" ",A1,LEN(A1)-5),
      IF(NOT(ISERROR(SEARCH(" ",A1,LEN(A1)-6))),SEARCH(" ",A1,LEN(A1)-6),
       IF(NOT(ISERROR(SEARCH(" ",A1,LEN(A1)-7))),SEARCH(" ",A1,LEN(A1)-7),0)))))))

This gives you the position of the last space, as long as the last word is not longer than 7 chars - you can add more if statements to make this longer, but there will have to be a limit somewhere. Then its just a matter of =RIGHT(A1,LEN(A1)-B1) to get the actual word.

This works even if there is only one word or the whole string is shorter than your maximum word length. Still, ugly...

Answer 4

There are 3 approaches I can think of:

1. Use a reverse lookup function to get location of the first space from right, use RIGHT to get the answer.
2. Use a for loop to get location of the last space in text, use RIGHT to get the answer.
3. Reverse the string, get location of the first space, use LEFT to get the answer and reverse it.

Sadly, all these approaches requires a bit of VBA to work.

Answer 5

I found this on google, tested in Excel 2003 & it works for me:

=IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))),A1)

[edit] I don't have enough rep to comment, so this seems the best place...BradC's answer also doesn't work with trailing spaces or empty cells...
[2nd edit] actually, it doesn't work for single words either...

Answer 6

This one is tested and does work (based on Brad's original post):

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",
   LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

If your original strings could contain a pipe "|" character, then replace both in the above with some other character that won't appear in your source. (I suspect Brad's original was broken because an unprintable character was removed in the translation).

Bonus: How it works (from right to left):
LEN(A1)-LEN(SUBSTITUTE(A1," ","")) - Count of spaces in the original string
SUBSTITUTE(A1," ","|", ... ) - Replaces just the FINAL space with a |
FIND("|", ... ) - Finds the absolute position of that replaced | (that was the final space)
Right(A1,LEN(A1) - ... )) - Returns all characters after that |

EDIT: to account for the case where the source text contains no spaces, add the following to the beginning of the formula:

=IF(ISERROR(FIND(" ",A1)),A1, ... )

making the entire formula now:

=IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",
    SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Or you can use the =IF(COUNTIF(A1,"* *") syntax of the other version.

Answer 7

I translated to PT-BR, as I needed this as well. Enjoy

(please note that I´ve changed the space to \ -- I needed the filename only of path strings)

=SE(ÉERRO(PROCURAR("\",A1)),A1,DIREITA(A1,NÚM.CARACT(A1)-PROCURAR("|", SUBSTITUIR(A1,"\","|",NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1,"\",""))))))

Answer 8

You have saved me learning VB :-)

Answer 9

This is very clean and compact, and works well.

{=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(1:999),1)=" ",ROW(1:999),0)))}

It does not error trap for no spaces or one word, but that's easy to add.

Gabriele-

edit:

this handles trailing spaces, single word, and empty cell scenarios. Have not found a way to break it.

{=RIGHT(TRIM(A1),LEN(TRIM(A1))-MAX(IF(MID(TRIM(A1),ROW($1:$999),1)=" ",ROW($1:$999),0)))}