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.
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:
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:
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:
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.
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!
Best Office Productivity Tools
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...
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!