Extract nth word from a text string

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.

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 glance, the formula above looks a little complicated, but don’t worry; we’ll explain it in a nutshell.

using-LEN-TRIM-REPT-MID-SUBSTITUTE-to-extract-nth-word-from-a-text

Explanation:

  1. =SUBSTITUTE(B3,” “,REPT(” “,LEN(B3))),(3-1)*LEN(B3)+1,LEN(B3): we’ll get additional spaces in cell B3 because the SUBSTITUTE and the REPT formula replace unnecessary characters with spaces. The total number of characters extracted is equal to the total length of the original text string.
  2. =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.
  3. =TRIM(MID(SUBSTITUTE(B3,” “,REPT(” “,LEN(B3))),(3-1)*LEN(B3)+1,LEN(B3))): Finally, use the TRIM function to remove unnecessary spaces from the cell.

Extract the nth word using a 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.