# OzGrid

Excel VBA: Extract Word From a Text String

Category: [Excel]  Demo Available

# Excel VBA: Extract Word From a Text String

## Custom Excel Formula/Function to Parse Words From a Text String in Excel

Got any Excel Questions? Free Excel Help.

Extract nth Word From Text in Excel See

With the aid of Excel VBA we can write a custom formula/function, or user defined function to extract out the nth word from a text string. The code below should be placed in a standard Excel Module after entering the VBE. That is, push Alt+F11 and then go to Insert>Module and paste in the code below;

```Option Compare Text

Function Get_Word(text_string As String, nth_word) As String

Dim lWordCount As Long

With Application.WorksheetFunction

lWordCount = Len(text_string) - Len(.Substitute(text_string, " ", "")) + 1

If IsNumeric(nth_word) Then

nth_word = nth_word - 1

Get_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
.Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _
.Find(" ", Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
.Find("^", .Substitute(text_string, " ", "^", nth_word)), 256)) - 2)

ElseIf nth_word = "First" Then

Get_Word = Left(text_string, .Find(" ", text_string) - 1)

ElseIf nth_word = "Last" Then

Get_Word = Mid(.Substitute(text_string, " ", "^", Len(text_string) - _
Len(.Substitute(text_string, " ", ""))), .Find("^", .Substitute(text_string, " ", "^", _
Len(text_string) - Len(.Substitute(text_string, " ", "")))) + 1, 256)

End If

End With

End Function```

Now come back to Excel proper and use in any cell like shown below. For all examples the full text string is in cell A1 and the text string is: Our main business focus is Excel Spreadsheets

=Get_Word(A1,"Last") would result in the word Spreadsheets as that word is the last word.

=Get_Word(A1,"First") would result in the word Our as that word is the 1st word.

=Get_Word(A1,6) would result in the word Excel as that word is the 6th word.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

### Gallery

stars (0 Reviews) 