Extract nth word from a text string

Updated on
Extract nth word from a text string

This tutorial will show you how to extract the nth word from a text string (a sentence or phrase) in Excel.

First, we will explain the method based on Excel’s built-in function library. After that, you will learn how to use the Extract_Nth_Word user-defined function instead of complex formulas.

Today’s tutorial is a part of our definitive guide on Excel Formulas.

Generic Formula to extract the nth word from a text string

If you are unfamiliar with user-defined functions, you can use built-in functions, and you must combine five regular functions (LEN, TRIM, SUBSTITUTE, REPT, and MID) to extract the nth word (nth=3) from the text string in cell B3.

=TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),(3-1)*LEN(B3)+1,LEN(B3)))

At first look, the formula above looks a little scary but don’t worry; we’ll explain it in a nutshell.

Explanation

We’ll get additional spaces in cell B3 because the SUBSTITUTE and the REPT formula replace unnecessary characters with spaces.

=SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),(3-1)*LEN(B3)+1,LEN(B3)

The total number of characters extracted is equal to the total length of the original text string.

using LEN TRIM REPT MID SUBSTITUTE to extract nth word from a text
=MID(SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),(3-1)*LEN(B3)+1,LEN(B3))

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 unnecessary spaces from the cell. The final formula looks like this:

=TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),(3-1)*LEN(B3)+1,LEN(B3)))

Extract the Nth word using a UDF (User Defined Function)

The name of the function, ExtractNthWord, speaks for itself!

Syntax:

=Extract_Nth_Word(text, nth_word)

Arguments:

  • text: cell reference
  • nth_word: the no. of the word that you want to extract
extract words from a text string using a user defined function

In the example below, you want to extract the third word from a text string, so the formula:

=Extract_Nth_Word(B3,3)

Insert the code into 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. Paste the code.

Download the practice Workbook that contains the function.

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 into words based on Delimiters

The built-in text-to-columns method can be helpful if you can split a text string into individual words using a specified delimiter.

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

Excel will split the entire string into separate columns. Each column will contain one word from the sentence.

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.