The formula demonstrated above in cell range C3:C9 extracts the last word from adjacent cell in column B.
=TRIM(RIGHT(SUBSTITUTE(B3, " ", REPT(" ", 200)), 200))
I was inspired by Rick Rothstein's comment from the year 2012 when I made this formula.
If your words are longer than 200 characters change each instance of 200 in the formula above to a higher value.
The delimiting character is a blank (space character). Make sure you change that if the cell value contains a different string delimiting character.
Explaining the formula in cell C3
The SUBSTITUTE function replaces each blank in the cell value to 200 blanks.
SUBSTITUTE(B3," ",REPT(" ",200))
SUBSTITUTE(B3," "," ") and returns "Martin Williams".
(I have shortened the string for obvious reasons.)
The RIGHT function extracts the 200 characters starting from the right side of the word (backward).
RIGHT(SUBSTITUTE(B3," ",REPT(" ",200)),200)
RIGHT("Martin Williams",200) and returns " Williams".
The TRIM function removes all leading and trailing spaces in a string or cell value.
TRIM(RIGHT(SUBSTITUTE(B3," ",REPT(" ",200)),200))
TRIM(" Williams") and returns Williams in cell C3.
Download Excel *.xlsx file
Extract last word in cell.xlsx