Extract nth word from text string

|
extract nth word from a text string in excel

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.

Generic Formula to extract the nth word from a text string

Explanation: How the formula works

=REPT(" ",LEN(B2))),(C2-1)

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!

extract words using user defined function

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.

  1. Click the “Data” tab on the ribbon
  2. Click the “Delimited” option button. A new dialog box will appear, click next.
  3. Click the “space” checkbox and the finish button.
extract nth word using text to columns

Excel will split the sentence you wrote into separate columns. Each column will contain one word from the sentence.

Related Formulas and Resources