Skip to main content

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!


Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with 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 - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download 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 - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download 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: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I've created the FindWord custom function as described and it worked perfectly but it stopped working the next day. I looked in the VBA window and the Module is still there. Any idea why the function is not working anymore?
This comment was minimized by the moderator on the site
Dark Chocolate 25gm box 12 pcs

Dark Chocolate 20gm*24 box

White Chocolate 15gm

White Chocolate 25gm*24

Biscuits W/Marshmallow300gm

Chocolate 40gm

Can some plz help to extract the numbers before "gm", for example : 25,20,15,25,300,40
This comment was minimized by the moderator on the site
Hi I would like to know how to use the VBA model but I have sentences of only one word, and i would like to pick the first word even if there is only one word.

Thank you
This comment was minimized by the moderator on the site
Hi Maura,
The VBA cannot extract the first word if there is only word in the cell. However, it’s recommended to apply the Text to Column to extract the first word of every cell in a column quickly.
1. Select the column, copy, and paste in a blank column.
2. Keep the new column selected, click Data > Text to Column.
3. In the Text column dialog, select Delimited, and then check Space as delimiters, and finally click Finish.
4. Now all words are separated by space. For the extracted words, you can remove all columns except the first one.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.)
This comment was minimized by the moderator on the site
Thank you! It works fine called in a Sub.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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)
This comment was minimized by the moderator on the site
Many thanks for sharing VBA code....saved me a lot of time
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations