How to extract first / last / nth word from text string in Excel?

If you need to extract the first word from a list of text strings, the following formulas can help you.

To extract the first word, please enter below formula into a blank cell, and then drag this cell's AutoFill handle to the range as you need.

=IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1))

To extract the last word from each cell, please apply this formula:

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

And now you will see the first or the last word is extracted from each cell.

Notes: In above formulas, A2 indicates the cell you will extract the first or the last word from.

Hard to memorize long complicated formulas? Amazing tool help you extract the nth Word with several clicks only!

Above long formulas can only extract the first and the last word, but will be useless to extract the specified nth word, says the second word, the sixth word, etc. Even you can figure out formulas to solve the problem, the formulas must be too complex to memorized and applied. Here, the Extract the nth word in cell feature of Kutools for Excel is recommended, and it will help you extract the nth word as easy as possible!

If you want to extract the second, third or any nth word from the text string, you can create a user defined function to deal with it.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

Function FindWord(Source As String, Position As Integer)
'Update 20131202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. Then save the code, in this example, I will get the third word from the string, so type this formula =findword(A2,3) into a blank cell B2, and then drag this cell's AutoFill handle to the range as you need. See screenshot:

Note: In the above formula, A2 is the cell where you want to extract word from, the number 3 indicates the third word in the string that you will extract, and you can change them as you need.

This method will introduce Kutools for Excel's Split Cells utility to extract each word from text string cells, and then list extracted words horizontally or vertically based on your need.

1. Select the text string cells that you will extract their words from, and click the Kutools > Merge & Split > Split Cells.

2. In the opening Split Cells dialog box, specify the split type in the Type section, check the Space option in the Specify a separator section, and click the Ok button. See screenshot:

3. Now specify the destination range that you will output extracted words into, and click the OK button

If you checked the Split to Columns option in the above Split Cells dialog, every word is extracted from every text string and listed vertically.

If you checked the Split to Rows option in the above Split Cells dialog, every word is extracted from every text string and listed horizontally.

If you have installed Kutools for Excel, you can use its Formula Helper > Extract the nth word in cell feature to quickly extract the nth word from the specified cell easily.

1. Select the cell you will put the extracted word at, and click Kutools > Formula Helper > Formula Helper to enable this feature.

2. In the Formulas Helper dialog, please do as follows:
(1) Select Text from the Formula type drop-down list;
(2) Click to highlight Extract the nth word in cell in the Choose a formula list box;
(3) In the Cell box, specify the cell that you will extract word from;
(4) In The Nth box, specify the number.

3. Click the Ok button. If necessary, please drag the AutoFill Handle of the formula cell and copy the formula to other cells.