How to remove first / last word from text string in cell?
In everyday Excel work, it's common to manage lists or sentences where you need to clean up text data. One frequent task is removing the first or last word from a text string within a cell. For example, you might have a column of full names or detailed descriptions, but for analysis, you only want to keep everything except the initial or final word. As shown in the screenshot below, Excel itself does not provide a built-in, direct feature for this operation. However, there are several practical methods you can use to achieve this efficiently, including formulas, Excel features, and VBA code. This guide introduces multiple approaches to solve this issue and helps you select the most suitable one for different scenarios.
Contents:
- Remove first or last word from text string with formulas
- Remove first or last word using “Text to Columns” (Built-in Excel Method)
- Remove first or last word with VBA Macro
Remove first or last word from text string with formulas
Using Excel formulas offers a straightforward, no-code way to remove the first or last word from a cell. This method is suitable when you need a dynamic approach that updates automatically if the original text changes. However, note that these formulas assume that words are separated by single spaces and that there are no leading, trailing, or multiple consecutive spaces in the text. Please confirm your data spacing before applying these solutions.
Remove the first word from text string:
1. Please enter this formula into a blank cell where you want to put the result (for example, cell B2):
=RIGHT(A2,LEN(A2)-FIND(" ",A2))Here, A2 is the cell containing your original text string. This formula locates the first space and removes the first word along with the space, returning only the remaining text.
2. After confirming the formula, drag the fill handle down to apply it to other cells in the column as needed. You'll notice that the first word is now removed from each text string as shown:
Tips: If there might be extra spaces at the beginning of your text (leading spaces), it's recommended to use the TRIM function to clean up the data before applying the above formula, for example:
=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(" ",TRIM(A2)))This ensures the formula remains accurate even if some cells have inconsistent spacing.
Remove the last word from text string:
To remove the last word from a cell, use the following formula. Enter it in a cell such as B2:
=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)This formula works by finding the location of the last space and extracting all text before it. Make sure there are no extra spaces between words, or use the TRIM function as above. After entering the formula, drag the fill handle to apply it to as many rows as required. All last words in the column will be deleted as demonstrated:
Error reminder: If a cell contains only one word and no spaces, these formulas will return either an error or a blank value. If your data might include such cases, you can wrap the formula with IFERROR to avoid errors, for example:
=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")Remove first or last word using “Text to Columns” (Built-in Excel Method)
In some cases, especially when processing shorter or static datasets, Excel’s built-in Text to Columns feature can provide an intuitive, visual approach to splitting words apart. This approach is ideal when you want to quickly separate all words from a text string into columns, and then selectively recombine them (leaving out the first or last word). Although it does not automatically update text when the source cell changes (not dynamic like formulas), it can be quite handy for one-time cleanups.
Steps:
- 1. Select the column containing your text data to split.
- 2. Go to the Data tab and click Text to Columns.
- 3. In the wizard, choose Delimited, then click Next.
- 4. Check only the Space delimiter and click Next.
- 5. Choose the destination for the split data, then click Finish.
- 6. After splitting, columns will be filled with individual words from each cell. To remove the first word, simply recombine all columns except the first using CONCATENATE or TEXTJOIN (if available). Similarly, to remove the last word, combine all except the last column.
You can use a formula like the following (assuming your split data is now in columns B, C, D for one cell):
=CONCATENATE(C2," ",D2)If you have many columns to join, and you have Excel 2016 or later, use:
=TEXTJOIN(" ",TRUE,C2:E2)Cautions: The Text to Columns method overwrites existing data to the right of the selected range if you’re not careful. Always ensure the adjacent columns are empty, or copy your data to a blank area first for safety. This method is best for quick, manual processing rather than automated updates.
Applicable Scenario Analysis: Good for quick, manual operations with short lists or when you need to visually review and correct your results. Not suitable for large, changing datasets where automation is desirable.
Remove first or last word from text string with VBA Macro
For more advanced or repeated bulk operations—such as cleaning up large datasets, automating removal, or handling complex scenarios—a VBA macro is very efficient. VBA allows you to remove the first or last word for an entire range of cells in just a few clicks. Before running the macro, ensure that your data does not contain abnormal spacing or special non-breaking spaces, as this may affect splitting accuracy.
How to use this VBA code:
1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications editor. In the new window, click Insert > Module. Paste one of the following codes into the Module.
Remove the first word from selected cells:
Sub RemoveFirstWord()
Dim WorkRng As Range
Dim Rng As Range
Dim arr As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to remove first word", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If InStr(Rng.Value, " ") > 0 Then
arr = Split(Rng.Value, " ")
Rng.Value = Mid(Rng.Value, InStr(Rng.Value, " ") + 1)
End If
Next
End Sub2. Press F5 or click Run to run the code. The macro then pops up a dialog box for you to select the cells you want to process. Click OK will update your cells directly by removing the first word from each selected cell.
Remove the last word from selected cells:
To remove the last word from selected cells, apply the following VBA code.
Sub RemoveLastWord()
Dim WorkRng As Range
Dim Rng As Range
Dim arr As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to remove last word", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If InStr(Rng.Value, " ") > 0 Then
arr = Split(Rng.Value, " ")
Rng.Value = Left(Rng.Value, Len(Rng.Value) - Len(arr(UBound(arr))) - 1)
End If
Next
End SubPrecautions: These macros remove only the first or last word separated by spaces in each cell of your selection. If a cell contains only one word (no spaces), that cell remains unchanged. Always work on a copy of your data or save your workbook before running VBA code, as changes are not reversible.
Advantages: VBA allows for rapid bulk processing and is highly effective for automating repetitive cleanup tasks, especially when handling large tables of data.
Troubleshooting: If you encounter "Run-time error" messages, ensure all values in your selection are text-formatted. Blank or error cells may cause the macro to skip or halt.
For whichever method you select—formulas, Text to Columns, or VBA—it’s recommended to always check your results for data consistency, especially when text might include extra spaces, punctuation, or unexpected word sequences. Back up your original data whenever possible, especially when applying bulk edits or unfamiliar macros. If you need more flexible or robust solutions for cleaning or splitting text in Excel, Kutools for Excel offers a comprehensive set of utilities that handle a variety of text-splitting and formatting challenges without complex formulas or code.
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 than300 handy Excel add-ins, free to try with no limitation in30 days. Download and free trial Now! |
Related article:
How to remove first, last or certain characters from text in Excel?
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in