How to extract first / last / nth word from text string in Excel?
Have you ever suffered with the problem that you need to extract a certain word from the text string in a worksheet? For example, you have the following range of text strings needed to get the first/last or nth word from them, here I can talk about some effective ways for you to solve it.
- Extract first word or last name from text string with formulas
- Extract nth word from text string with User Defined Function
- Extract each word from text string and list them horizontally or vertically
- Extract nth word from text string with an amazing tool
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.
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.
- How to extract text before/after space or comma only in Excel?
- How to split text by space/comma/delimiter in Excel?
- How to extract text between commas/brackets/parentheses in Excel?