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.
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:
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.
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.
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.
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
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([b]A2,LEN[/b](A2)-FIND(" ",A2)) Why???