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

Question

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.

When the original string might contain a space at the last position add a trim function while counting all the spaces: Making the function the following:

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

This is the technique I've used with great success:

=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

To get the first word in a string, just change from RIGHT to LEFT

=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

Also, replace A1 by the cell holding the text.

A more robust version of Jerry's answer:

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

That works regardless of the length of the string, leading or trailing spaces, or whatever else and it's still pretty short and simple.

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...