# 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.

``=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

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.

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.

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.