How to Extract Nth Word from Text String Using VBA in Microsoft Excel

 

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

img1

  • Click on Insert, and then on Module

img2

  • 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 = “”

    Else

ExtractNthWord = word(y – 1)

    End If

End Function

img3

 

  • 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:

 

img4

 

  • To retrieve 2nd word from column A, the formula will be =ExtractNthWord(A2,2)

 

img5

 

  • Similarly, to extract 3rd word, the formula will be =ExtractNthWord(A2,3)

 

img6

 

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

 

img7

 

Now, we only need to change the number in cell D1 & the results will automatically be changed accordingly.



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>