Author: Oscar Cronquist Article last updated on March 25, 2018

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

becomes

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)

becomes

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

becomes

TRIM("      Williams") and returns Williams in cell C3.

Download Excel *.xlsx file

Extract last word in cell.xlsx