In this tutorial, we’ll show you how to extract the nth word from a text string (from a sentence or phrase). We’ll explain two solutions, a combination of functions and a much better trick using the Extract_Nth_Word function.
Generic Formula to extract the nth word from a text string
We’ll combine five Excel functions (LEN, TRIM, SUBSTITUTE, REPT, and MID) to extract the third word from the text string in cell B2.
=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(C2-1)*LEN(B2)+1,LEN(B2)))
As a first look, the formula a little bit complex. Don’t worry, we’ll explain it in a nutshell.
Explanation: How the formula works
We’ll get additional spaces in cell B2 because the SUBSTITUTE and the REPT formula replaces the unnecessary characters to spaces.
=SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(C2-1)*LEN(B2)+1,LEN(B2)
Now the total number of characters extracted is equal to the total length of the original text string.
=MID(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(3-1)*LEN(B2)+1,LEN(B2)))
The expression uses the MID function to extract the third word from the text. So we can add the “3” as a parameter of the following formula.
Finally, use the TRIM function to remove the unnecessary spaces from the cell. The final formula looks like:
=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(3-1)*LEN(B2)+1,LEN(B2)))
Extract Nth word with User Defined Function
The name of the function ExtractNthWord speaks for itself!
Place the code below in a standard Excel Module. To enter the Visual Basic Editor (VBE is a simple developer environment), press Alt+F11 and then go to Insert>Module.
Finally, paste the code.
Function Extract_Nth_Word(str As String, n As Integer) As String Dim Current_Pos As Long Dim l_str As Integer Dim word_no As Integer Extract_Nth_Word = "" word_no = 1 str = Trim(str) l_str = Len(str) For Current_Pos = 1 To l_str If (word_no = n) Then Extract_Nth_Word = Extract_Nth_Word & Mid(str, Current_Pos, 1) End If If (Mid(str, Current_Pos, 1) = " ") Then word_no = word_no + 1 End If Next Current_Pos Extract_Nth_Word = Trim(Extract_Nth_Word) End Function
Split the text to words based on Delimiters
The built-in text to columns method can be useful if you are not familiar with function or VBA programming. You can split a text string into individual words using a specified delimiter.
- Click the “Data” tab on the ribbon
- Click the “Delimited” option button. A new dialog box will appear, click next.
- Click the “space” checkbox and the finish button.
Excel will split the sentence you wrote into separate columns. Each column will contain one word from the sentence.
Related Formulas and Resources
- How to clean data in Excel?
- Get first word
- Get last word from some text
- Split text with delimiter
- Abbreviate names in Excel