Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

Extract nth word from text string with User Defined Function

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

Easily mass extract the first names and last names from full names in Excel

Kutools for Excel's Split Names utility can help you quickly mass split full names to first names, middle names, and last names with several clicks in Excel. Full Feature Free Trial 30-day!
ad split names 2

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Extract first word or last name from text string with Formulas

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

To extract the first word, please enter this formula =IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1)) into a blank cell, and then drag this cell's AutoFill handle to the range as you need.

And now you will see the first word is extracted from each cell. See screenshot below:

Notes:

(1) In above formula, A2 indicates the cell you want to apply this formula.

(2) For extracting the last words from each cell, please apply this formula: =IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))).

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

arrow blue right bubble 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.


arrow blue right bubble 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.

1. Select the text string cells that you will extract their words, and click the Kutools > Text > 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:
Note: To list all extracted words of each text string cell horizontally, please check the Split to Columns option; to list all extracted words of each text string cells vertically, please check the Split to Rows option. In our case, we check the Split to Rows option.
   

3. In opening new dialog box, select a single cell of range that you will output extracted words into, and click the OK button. See screenshot above:

Then you will see all words are extracted from each text string cell and listing vertically as below screenshot shown:

Checking Split to columns option will horizontally list all extracted words from each text string as below screenshot shown:

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


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

arrow blue right bubbleRelated articles:


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.
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
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    murali · 1 years ago
    i want to remove the last word in cell and data is like this "/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/Cdtr/CtctDtls/FaxNb" can i have formula
  • To post as a guest, your comment is unpublished.
    John · 2 years ago
    Thanks so much for these! I have a question, though: how do I extract a number or group of numbers from a cell or textbox? For instance, if I have [37.5" x 21'] in cell A1, how could extract 37.5 into A2? There could be any number of characters and numbers in A1, but I'll always want the first number to the left of the "X", and the first number to the right. Not sure if you've covered something like this elsewhere, so I thought I'd ask here.
    (Not sure if this went through the first time, so here it is again.)
  • To post as a guest, your comment is unpublished.
    Antonio · 2 years ago
    Thank you! It works fine called in a Sub.
  • To post as a guest, your comment is unpublished.
    DC2209 · 3 years ago
    the original formula isn't working for me, I keep getting #NAME?, anyone able to help? I am using Excel 2013 and this VBA would save me sooooo much time
  • To post as a guest, your comment is unpublished.
    DC2209 · 3 years ago
    The original VBA code posted here would save me so much time, however whenever I type in the =findword formula, I then get #NAME?, anyone able to help me? I am using Excel 2013
  • To post as a guest, your comment is unpublished.
    Shiva · 3 years ago
    Hi,

    I am having cell with values like SAN_UN_TC1,SAN_UN_TC2,PEP_HR_TC1
    I would like to extract first words liek SAN /PEP into one cell and words liek UN/HR to another cell
    How could i do it, Any help is appreciated.

    Thanks,
    Shiva
    • To post as a guest, your comment is unpublished.
      Allart · 3 years ago
      If number of characters in string (SAN_UN_TC1) is fixed (3_2_3), it's simple:
      column 1: =LEFT(B15,3)
      column 2: =MID(B15,5,2)
      column 3: =RIGHT(B15,3)
  • To post as a guest, your comment is unpublished.
    Dean · 3 years ago
    Many thanks for sharing VBA code....saved me a lot of time
  • To post as a guest, your comment is unpublished.
    Dean · 3 years ago
    VBA is so useful - thanks for sharing!
  • To post as a guest, your comment is unpublished.
    Ron Hollingsworth · 3 years ago
    I would like to extract the first word on A1 and put it in front of a word in B1. what's the formula to do that? Thanks!
  • To post as a guest, your comment is unpublished.
    Lorie · 3 years ago
    I'm looking for a formula that will pull out the First and Last Name in a text string. I've tried the formula here, which is amazing by the way, and it works great until it comes across a surname that is not hyphenated. Here is a sample of the text strings I'm presented with:
    example 1: Mouse, Mickey Justin M Feb 10
    example 2: Hall Mouse, Minnie Jean F Mar 18

    For string example 1, the formula/function here works great. For example 2 I get "Hall, Mouse".

    Here is the Excel formula I'm running:
    =CONCATENATE(LEFT(A2,FIND(", ",A2)+1), 'Personal Macro Workbook'!FindWord(A2,2))

    Is there a way to modify the user defined function so that it looks for the first word to the right of the ","?
    • To post as a guest, your comment is unpublished.
      Allart · 3 years ago
      Remove the last name from the string you feed to the FindWord function:
      - find the comma position
      - subtract this from the total length of the string
      - use the remaining 'RIGHT' side of the original string

      =CONCATENATE(LEFT(A2,FIND(", ",A2)+1),FindWord(RIGHT(A2,LEN(A2)-FIND(", ",A2)-1),1))
  • To post as a guest, your comment is unpublished.
    Dan · 3 years ago
    First word. Copy/paste the column, select the contents of the new column, replace " *" with "".
    Last word. Copy/paste the column, select the contents of the new column, replace "* " with ""
  • To post as a guest, your comment is unpublished.
    Vishal · 4 years ago
    ITS WORKING that's good.
    really helpful for us.
  • To post as a guest, your comment is unpublished.
    Mark Bolton · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Thang Nguyen · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Allart · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    cheezfri · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Angelia · 4 years ago
    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..
  • To post as a guest, your comment is unpublished.
    Nico · 4 years ago
    Thank You soooo MUCH!!
  • To post as a guest, your comment is unpublished.
    Badan · 4 years ago
    Thanks, Alan, this is great, thanks! :)
  • To post as a guest, your comment is unpublished.
    Allart · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Allart · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Mohammad · 4 years ago
    Much appreciated. It helped me a lot.
  • To post as a guest, your comment is unpublished.
    yücel · 4 years ago
    how can we change fonts for nth word in a cell
  • To post as a guest, your comment is unpublished.
    MikeSyr · 4 years ago
    This does not work properly on a source string made up of non-words (like vendor names followed by part numbers)
  • To post as a guest, your comment is unpublished.
    Badan12 · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Balu · 5 years ago
    Good one. This worked very well
  • To post as a guest, your comment is unpublished.
    Balu · 5 years ago
    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...
  • To post as a guest, your comment is unpublished.
    baynes@ecg-inc.com · 5 years ago
    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-
  • To post as a guest, your comment is unpublished.
    Chad · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Alexandros · 5 years ago
    Thanks a lot! Worked fine! :-)
  • To post as a guest, your comment is unpublished.
    Jarad · 5 years ago
    This user-defined function is awesome! Thanks a ton for sharing it.
  • To post as a guest, your comment is unpublished.
    Edgar · 5 years ago
    That vba code is fantastic, thanks a lot.