In this article, you will learn how to extract the nth word from text string. We will use VBA code to create UDF to find the nth word in a string.
We need to follow the below steps:
- Click on Developer tab
- From Code group, select Visual Basic
- Click on Insert, and then on Module
- This will create new module.
Enter the following code in the Module
Function ExtractNthWord(x As String, y As Integer)
Dim word() As String
Dim wordCount As Long
word = VBA.Split(x, ” “)
wordCount = UBound(word)
If wordCount< 1 Or (y – 1) >wordCount Or y < 0 Then
ExtractNthWord = “”
ExtractNthWord = word(y – 1)
- The new UDF formula is created with name ExtractNthWord
- There are two parameters; first is the lookup cell that needs to be extracted data from & second is the nth number.
Following is the snapshot of the data:
- To retrieve 2nd word from column A, the formula will be =ExtractNthWord(A2,2)
- Similarly, to extract 3rd word, the formula will be =ExtractNthWord(A2,3)
- We can use helper cell to store the value of the second argument; this will allow us to see the results faster.
- Here, helper cell is D1 wherein we can enter the nth number of word that we want to see as output.
Now, we only need to change the number in cell D1 & the results will automatically be changed accordingly.