# 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. Back to:** **
Excel Custom Function/Formulas
**.

**Extract nth Word From Text in Excel See**

Also: Extracting Words From Text
in Excel using Excel Built in Function/Formulas and
Find Nth Occurrence

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.

**Excel
Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money**

**Special! Free
Choice** of
Complete Excel Training Course **OR **
Excel Add-ins Collection
on all purchases totaling over $64.00. ALL
purchases totaling
over $150.00 **gets you BOTH! Purchases MUST be made via
this site**. Send payment proof to [email protected] 31 days after purchase
date.

**Instant Download and Money Back Guarantee on Most Software**

**
Excel Trader Package
**Technical Analysis in Excel With $139.00 of **FREE software!**

**Some of our more popular products are below...**

**Convert** Excel** Spreadsheets To Webpages** | **Trading** In Excel | Construction **Estimators** | **Finance** Templates & Add-ins **Bundle **| Code-**VBA**** **| Smart-**VBA** | **Print**-VBA | Excel **Data Manipulation & Analysis** | **Convert** MS Office Applications To...... | **Analyzer** Excel | **Downloader** Excel
| **MSSQL Migration**
Toolkit |
**
Monte Carlo** Add-in |
Excel **
Costing Templates**