Excel: Find the last word in a cell
A friend needed a quick hand the other day, to get just the last word from a cell. He was splitting names into First / Last name, but didn’t want to use Text to Columns because the people on his list had anywhere between zero and three middle names.
Here’s a simple formula to return the last word in a cell:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
If you have “Eric Light” in cell A1, that will return just the word “Light”.
Thanks to ExcelTip.com’s post here: http://www.exceltip.com/st/Extract_the_last_word_in_a_cell_in_Microsoft_Excel/368.html
David
August 9, 2011 @ 10:28 am
Elegant. I’d used the len of cell – length of substitute cell before to find the number of instances of a substring in a string, and this is a nice logical extension.
One trap for the unwary, depending on where your data comes from, you may want to trim it (just replace all instances of “A1” with “TRIM(A1)”), because with the above formula, the string “Eric Light ” returns nothing 🙁
I still have difficulty with the fact the excel does not have a function for “how many times does substring appear in string”!
One of the other languages I work with, the “substitute” function (called something else entirely) takes positive or negative numbers for “instance_num”, negative numbers indicating instances from the right! Very helpful, was hoping this would come with Excel 2007 but no luck. Haven’t actually tried it on 2010…….