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.

Step

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.

Step

Click in cell “C2.” Enter the following formula and press the “Enter” key:

Step

\=MID(SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,256)

Step

You can copy and paste this formula, adjusting the C2 cell reference as necessary. The entire formula goes into one cell.

Step

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.