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

Question

I have a list of strings in an Excel spreadsheet. There are multiple words in each string, however, the total number of words in each string varies.

Is there a method to identify the final word in each string using built-in Excel operations (no VBA)?

Examples:

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

Answer 1

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," ","")))))

Replace both of the above characters with a different character that won't exist in your source if your original strings could have contained the pipe "|" character. (I believe Brad's original was incorrect because the translation left out an unprintable character.)

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 |