Excel - Last Word

Two ways to get the last word (2 similar formulas one with Mid and the other with Right functions):

1)
Enter the following text in cell A1: Are you going to Disneyland?
Use the Following Function:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
The result: Disneyland?


2)
=MID(A1,FIND(CHAR(22),SUBSTITUTE(A1," ",CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)

This will return the last word in a phrase separated by blanks, so will work on 2, 3, or more part names.

The LEN(A1)-LEN(SUBSTITUTE(A1," ","")) temporarily changes blanks to nulls, and by subtracting these 2 you find out how many blanks there are. This is passed to the SUBSTITUTE function using a CHAR(22) (somewhat arbitrary) to replace the LAST blank with this character. Then I FIND the location of this CHAR(22), the last blank, and add 1 to get the first position after that character. That's passed to the MID function.


Credits: Excel Tip AllExperts