How to Extract the Last Word in a String of Text in Microsoft Excel
In Microsoft Excel, you may have a list of individuals' names, consisting of first and last name, but some may include a middle name, initials or even a hyphenated name. You want to extract just the last name for each individual. Using a formula, you can “extract” or pull out just the last name when it is the last word in the string. The same technique can be used to extract the last word in any string of text in Microsoft Excel.
Open the Microsoft Excel worksheet that has the column of text from which you want to extract the last word. As an example, you want to extract the last name from a list of names in column A and insert it into column C.
Click in cell “C2.” Enter the following formula and press the “Enter” key:=MID(SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,256)You can copy and paste this formula, adjusting the C2 cell reference as necessary. The entire formula goes into one cell.
Once you have entered the formula, you can quickly fill the formula down through subsequent cells. To do that, hover your mouse over the black square in the lower right corner of the cell. When the mouse pointer changes to a “+” symbol, click your mouse and drag down.
Tips & Warnings
- Just as you can extract the last word in a string as explained in this article, it’s also possible to extract the first word from a string.