Skip to main content

How to remove first / last word from text string in cell?

If you have a column of text strings in your worksheet, and you need to remove the first or the last word from the text string in cell as following screenshot shown. Excel doesn’t provide a direct way for you, but, with the following formulas this tutorial show, this problem will be solved as soon.

doc-remove-first-word-1

Remove first or last word from text string with formulas

arrow blue right bubble Remove first or last word from text string with formulas

The following formulas may help you to delete the first or last word from the text cell, please do as this:

Remove the first word from text string:

1. Please enter this formula into a blank cell where you want to put the result: =RIGHT(A2,LEN(A2)-FIND(" ",A2)) (A2 is the cell which has the text string you want to remove the first word), see screenshot:

doc-remove-first-word-2

2. And then drag the fill handle down to the range that you want to apply this formula, and the first words have been removed from the cells, see screenshot:

doc-remove-first-word-3

Remove the last word from text string:

To remove the last word from the cell, please copy and paste this formula: =LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1), and then drag the fill handle over to the range that you want to contains this formula, and all the last words have been deleted from the cell. See screenshot:

doc-remove-first-word-4

Split Cell values into multiple columns and rows by specific separator:

With Kutools for Excel’s Split Cells utility, you can quickly split text string in a cell into multiple columns or rows  by space, comma, new line and any other separators you specified.

doc-split-cells-1 1

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related article:

How to remove first, last or certain characters from text in Excel?

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/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   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
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 Toolsets:  12 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

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 

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! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 
Comments (17)
No ratings yet. Be the first to rate!
Christine
Christine
This comment was minimized by the moderator on the site
Thank you so much!!
Reply
0
0
Shizelle
Shizelle
This comment was minimized by the moderator on the site
You're amazing!!
Reply
0
0
cyborg9512
cyborg9512
This comment was minimized by the moderator on the site
Hey, please assist me.



i have this: FPJCRTXCB540AS_CRG116



How do i keep the word before underscore, but remove the word after underscore?



tested and change to relevant cell but it shows "#value!". i just want it to be like this: FPJCRTXCB540AS
Reply
Report
0
0
skyyang
skyyang     cyborg9512
This comment was minimized by the moderator on the site
Hello,
To remove all text after the underscore, please apply the following formula:
=LEFT(A1, FIND("_", A1&"_")-1)

Please try, hope it can help you!
Reply
Report
0
0
Jack
Jack
This comment was minimized by the moderator on the site
Hey, please assist me.

i have this: FPJCRTXCB540AS_CRG116

How do i keep the word before underscore, but remove the word after underscore?

tested and change to relevant cell but it shows "#value!". i just want it to be like this: FPJCRTXCB540AS
Reply
0
0
Jacqueline
Jacqueline
This comment was minimized by the moderator on the site
Thank you so much for sharing the formula, very helpful!
Reply
0
0
Muhammad Talha
Muhammad Talha
This comment was minimized by the moderator on the site
Retention Payable - Tooba Aluminium & Glass Works. L.L.C

I want to remove (retention payable - ) from the above sentence

what I will do I want to save my time
Reply
0
0
Suman
Suman     Muhammad Talha
This comment was minimized by the moderator on the site
Just do find and replace if you just want to remove the same word from the sheet
saves time
Reply
0
0
skyyang
skyyang     Muhammad Talha
This comment was minimized by the moderator on the site
Hello, Muhammad,
To solve your problem, please apply the following formula:

=MID(A1,1+FIND("~",SUBSTITUTE(A1," ","~",3)),255)

Please try it, hope it can help you!
Reply
Report
0
0
Mark K
Mark K
This comment was minimized by the moderator on the site
I have two strings, one works fine removing the last word (the file size) but one does not (I think due to double space in the string.)

=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)

Cell A2 contents: D:\Sowdeswari\BIO-LAB-DSM-03\03-Jan-2019\Config\SecEvent.Evt 01/01/2019 5963776
Result: D:\Sowdeswari\BIO-LAB-DSM-03\03-Jan-2019\Config\SecEvent.Evt 01/01/2019 (This is correct)

Cell A2 contents: D:\laptop_swap\win_account\Desktop\ !!!Docs\FORM-00538.pdf 18/01/2018 298032
Result: D:\laptop_swap\win_account\Desktop\ !!!Docs\FORM-00538.pdf (this is not correct, but works when I remove one of the spaces before the "!!!" in the string)

All help appreciated. It's a large file and there are a few errors but if I can fix this I can probably work out the other ones.
Reply
0
0
Mick H
Mick H
This comment was minimized by the moderator on the site
Wow, thank you, worked perfectly and saved a lot of time.
Reply
0
0
marlonparinacruz@gmail.com
This comment was minimized by the moderator on the site
hello
may i ask you how to separate using formula the 4-digit numbers?
1234 · Bike:5678 · Tires Improvement; :6767
Reply
Report
0
0
megz
megz
This comment was minimized by the moderator on the site
AMINODDIN MAMONDIONG/HASSANA AMPUAN <<--- i want to separate this two names damn! let me try
Reply
0
0
ktsnj
ktsnj
This comment was minimized by the moderator on the site
Worked perfectly! Thank you very much.
Reply
Report
0
0
JAMES
JAMES
This comment was minimized by the moderator on the site
INSTEAD OF "," TRY USING ";"
Reply
0
0
vickyf.
vickyf.
This comment was minimized by the moderator on the site
hi! my problem is how to extract these names into first name , middle initial followed by last name. Roxas, Richard Jr. Alvarez Chavez, Ryan James Galareta Legaspi, Christian Arnaiz Jimenez, Johnray Del Castillo De Chavez, Rosalyn David Dela Rosa, Ana Joy Ramirez Manolo, Ma. Ana Santos Santillan, Jeneth Umali Result should be: Richard A. Roxas Jr. Ryan James G. Chavez Christian A. Legaspi Johnray D. Jimenez Rosalyn D. De Chavez Ana Joy R. Dela Rosa Ma. Ana S. Manolo Jeneth U. Santillan
Reply
0
0
Michael M.
Michael M.
This comment was minimized by the moderator on the site
Hi! I have issues applying the formula for removing the first word =RIGHT(A2,LEN(A2)-FIND(" ",A2)) When I insert it into Excel it opens an error window, saying that the formula is incorrect and marking =RIGHT(A2,LEN(A2)-FIND(" ",A2)) Why???
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations