Note: The other languages of the website are Google-translated. Back to English
English English

How to extract first / last / nth word from text string in Excel?

Have you ever suffered with the problem that you need to extract a certain word from the text string in a worksheet? For example, you have the following range of text strings needed to get the first/last or nth word from them, here I can talk about some effective ways for you to solve it.


Extract first word or last name from text string with Formulas

If you need to extract the first word from a list of text strings, the following formulas can help you.

To extract the first word, please enter below formula into a blank cell, and then drag this cell's AutoFill handle to the range as you need.

=IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1))

To extract the last word from each cell, please apply this formula:

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

And now you will see the first or the last word is extracted from each cell.

Notes: In above formulas, A2 indicates the cell you will extract the first or the last word from.

Hard to memorize long complicated formulas? Amazing tool help you extract the nth Word with several clicks only!

Above long formulas can only extract the first and the last word, but will be useless to extract the specified nth word, says the second word, the sixth word, etc. Even you can figure out formulas to solve the problem, the formulas must be too complex to memorized and applied. Here, the Extract the nth word in cell feature of Kutools for Excel is recommended, and it will help you extract the nth word as easy as possible! Full Feature Free Trial 30-day!

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Extract nth word from text string with User Defined Function

If you want to extract the second, third or any nth word from the text string, you can create a user defined function to deal with it.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

Function FindWord(Source As String, Position As Integer)
'Update 20131202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. Then save the code, in this example, I will get the third word from the string, so type this formula =findword(A2,3) into a blank cell B2, and then drag this cell's AutoFill handle to the range as you need. See screenshot:

Note: In the above formula, A2 is the cell where you want to extract word from, the number 3 indicates the third word in the string that you will extract, and you can change them as you need.


Extract each word from text string and list them horizontally or vertically

This method will introduce Kutools for Excel's Split Cells utility to extract each word from text string cells, and then list extracted words horizontally or vertically based on your need.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select the text string cells that you will extract their words from, and click the Kutools > Merge & Split > Split Cells.

2. In the opening Split Cells dialog box, specify the split type in the Type section, check the Space option in the Specify a separator section, and click the Ok button. See screenshot:

3. Now specify the destination range that you will output extracted words into, and click the OK button

If you checked the Split to Columns option in the above Split Cells dialog, every word is extracted from every text string and listed vertically.

If you checked the Split to Rows option in the above Split Cells dialog, every word is extracted from every text string and listed horizontally.


Extract the nth word from text string in Excel with an amazing tool

If you have installed Kutools for Excel, you can use its Formula Helper > Extract the nth word in cell feature to quickly extract the nth word from the specified cell easily.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select the cell you will put the extracted word at, and click Kutools > Formula Helper > Formula Helper to enable this feature.

2. In the Formulas Helper dialog, please do as follows:
(1) Select Text from the Formula type drop-down list;
(2) Click to highlight Extract the nth word in cell in the Choose a formula list box;
(3) In the Cell box, specify the cell that you will extract word from;
(4) In The Nth box, specify the number.

3. Click the Ok button. If necessary, please drag the AutoFill Handle of the formula cell and copy the formula to other cells.


Demo: extract each word from text string and list them horizontally or vertically


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
That vba code is fantastic, thanks a lot.
This comment was minimized by the moderator on the site
This user-defined function is awesome! Thanks a ton for sharing it.
This comment was minimized by the moderator on the site
Thanks a lot! Worked fine! :-)
This comment was minimized by the moderator on the site
This was SUPER helpful, I was wondering if anyone knew of a downloadable, or a copy and pastable list of helpful hand made functions such as the "FindWord" that I would be able to use.
This comment was minimized by the moderator on the site
Hi, I was just wondering what else I could insert into the Visual Basic formula that could extract and commas or dashs immediatly after the data.. for e.g. Fortin-
This comment was minimized by the moderator on the site
Hi, This is excellent. It worked very well and reduces our strain saves lots of time in this operation. Thanks to all who worked on this and shared...
This comment was minimized by the moderator on the site
Good one. This worked very well
This comment was minimized by the moderator on the site
Hi, This really saves a lot of time. Thanks for sharing Can someone also suggest how we can extract the words with vba in a reverse way, e.g last word is 1, the second from right to left is 2 and so on. This will be much appreciated.
This comment was minimized by the moderator on the site
This does not work properly on a source string made up of non-words (like vendor names followed by part numbers)
This comment was minimized by the moderator on the site
how can we change fonts for nth word in a cell
This comment was minimized by the moderator on the site
Much appreciated. It helped me a lot.
This comment was minimized by the moderator on the site
Hi, Thanks for your piece of code. It inspired me to expand a little. With the next lines, you can select: last word (0), one but last (-1), second to last (-2) etc. Regardless of number of words. Function FindWord(Source As String, Position As Integer) 'Update 20150504 Dim arr() As String arr = VBA.Split(Source, " ") xCount = UBound(arr) Select Case Position Case -xCount To 0 FindWord = arr(xCount + Position) Case 1 To (xCount + 1) FindWord = arr(Position - 1) Case Else FindWord = "" End Select End Function
This comment was minimized by the moderator on the site
Hi, just an addition to the code yesterday. This adds an extra line to adjust for redundant spaces in the source string: arr = VBA.Split(WorksheetFunction.Trim(Source), " ") regards, Allart
This comment was minimized by the moderator on the site
Thanks, Alan, this is great, thanks! :)
This comment was minimized by the moderator on the site
Thank You soooo MUCH!!
This comment was minimized by the moderator on the site
Hi.. Thanks for your code, i have list sentences, and one of them just 1 word, and your code not work if its just only have 1 word..
This comment was minimized by the moderator on the site
I can't get the code for the first word to work if some of my list has only one word in the cell. e.g. first word ---> first second ---> [null] last word ---> last
This comment was minimized by the moderator on the site
Try this (see my reply above): Function FindWord(Source As String, Position As Integer) ' gets 1st, 2nd, last(0), second to last(-2) etc, word from a string ' Update 20150505 Dim arr() As String arr = VBA.Split(WorksheetFunction.Trim(Source), " ") xCount = UBound(arr) Select Case Position Case -xCount To 0 FindWord = arr(Position + xCount) Case 1 To xCount + 1 FindWord = arr(Position - 1) Case Else FindWord = "" End Select
This comment was minimized by the moderator on the site
Hi geeks, I have a cell like this: 1 ABCD; 2 DEDF; 1 DED#3d; 4 FDWDS; 1 ED And I would like to extract character after 1. So desired to have a result as below: ABCD;DED#3d;ED
This comment was minimized by the moderator on the site
I have 64bit Excel 2013 and am missing the Morefunc addin. Based on the code above, I have come up with this replacement for Morefunc's WMID function. I would be grateful if anyone more expert than me could perform any tidying of it. Function WMid1(Source As String, Optional Position As Integer, Optional WordCount As Integer, Optional Separator As String) Dim arr() As String Dim xCount As Integer Dim wCount As Integer Dim wFirst As Integer Dim wLast As Integer If Position = 0 Then Position = 1 If WordCount = 0 Then WordCount = 1 If Separator = "" Then Separator = " " arr = VBA.Split(Trim(Source), Separator) xCount = UBound(arr) + 1 If Position < 1 Then wFirst = Application.Max(xCount + Position - WordCount + 1, 0) wLast = xCount + Position Else wFirst = Position - 1 wLast = Application.Min(xCount - 1, Position + WordCount - 2) End If If xCount < 2 Or Abs(Position) > xCount Then WMid1 = "" Else WMid1 = arr(wFirst) For wCount = wFirst + 1 To wLast WMid1 = WMid1 & Separator & arr(wCount) Next End If WMid1 = Trim(WMid1) End Function
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations