Tag Archives | Extract Last Name

Finding the Last Space Character in an Excel cell

I am often asked in my Excel Advanced User course if it is possible to extract just a person’s last name from a cell containing their full name.

Yes it is but it is not as simple as using the RIGHT function. As people can have middle names or initials the cell could contain more than one space character to deal with. Moreover, the text might contain leading or trailing spaces that need to be removed so the specific last space between names can be found.  This is done using the TRIM function.

The formula shown below removes leading and trailing spaces then, using the REPT function, substitutes for each space found repeating spaces for the number of characters in the trimmed text. With the now very expanded text that has lots of spaces in blocks within it, we use the RIGHT function to extract the text from the expanded string for the length of the original trimmed string will give the last name with a load of leading spaces. Finally the last TRIM function removes any leading spaces, leaving the last name intact. This formula also handles having leading and trailing spaces and also circumstance where there is no space in the cell. The formula assumes the full name is in cell A1. Please adjust the cell references to fit your circumstances.

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

(All the above is typed on one line!)