Wednesday, February 27, 2013

How to Extract the Last Word in a String of Text in Microsoft Excel


1. 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.
2. 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.
3. 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.

Blogger news