Remove the first or last word from a string
This tutorial explains how to remove only the first or last word from the text string in a cell with formulas in Excel.
Remove the first word from the string in a cell
Remove the last word from the string in a cell
Remove the first word from the string in a cell
In this section you will learn how to use a combination of the RIGHT, LEN and FIND functions to remove the first word from the string in a cell.
Generic formula
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
Arguments
A1: Represents the cell containing the text string you will remove the first word from.
How to use this formula?
1. Select a blank cell to output the result.
2. Enter the below formula into it and press the Enter key. And then drag its Fill Handle down to apply the formula to other cells.
=RIGHT(B3,LEN(B3)-FIND(" ",B3))
Note: In this formula, B3 is the cell containing the text string you want to remove the first word. Please change it as you need.
How this formula works?
=RIGHT(B3,LEN(B3)-FIND(" ",B3))
1. LEN(B3): The LEN function calculates the total length of text string "Mr ana varela (Home)" and returns the result as 20;
2. FIND(" ",B3): As the words are separated by space, here the FIND function locates the position of the first space in text string "Mr ana varela (Home)" and returns the result as 3;
3. RIGHT(B3,20-3): The RIGHT function extracts 17 characters (20-3=17) from the right side of the text string "Mr ana varela (Home)". The result is "ana varela (Home)".
Remove the last word from the string in a cell
If you want to remove the last word from the text string in a cell, you can do as follows.
Generic formula
=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)
Arguments
A1: Represents the cell containing the text string you will remove the last word from.
How to use this formula?
1. Select a blank cell to place the result. In this case, I select cell D3.
2. Enter the below formula into it and press the Enter key. Select the result cell and then drag its Fill Handle down to apply the formula to other cells.
=LEFT(TRIM(B3),FIND("~",SUBSTITUTE(B3," ","~",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3)," ",""))))-1)
Note: In the formula, B3 is the cell containing the text string you want to remove the last word. Please change it as you need.
How this formula works?
=LEFT(TRIM(B3),FIND("~",SUBSTITUTE(B3," ","~",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3)," ",""))))-1)
1. TRIM(B3): The TRIM function removes all extra spaces from the string "Mr ana varela (Home)" and only keeps single space between words. Here the result is "Mr ana varela (Home)";
2. FIND("~",SUBSTITUTE(B3," ","~",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3)," ",""))))
- LEN(TRIM(B3)): As the TRIM function returns the result as "Mr ana varela (Home)", the LEN function returns the number of characters in the string "Mr ana varela (Home)" and returns the result as 20;
- LEN(SUBSTITUTE(TRIM(B3)," ","")): The SUBSTITUTE function replaces all spaces in text string "Mr ana varela (Home)" with nothing and returns the result as "Mranavarela(Home)". And then the LEN function calculates the total length of "Mranavarela(Home)" and gets the result of 17;
- SUBSTITUTE(B3," ","~",20-17): Here the SUBSTITUTE function replaces the third space (20-17=3) in text string "Mr ana varela (Home)" with a character "~". Here the result is "Mr ana varela~(Home)".
- FIND("~","Mr ana varela~(Home)"): The FIND function returns the position of the character "~" in the text string "Mr ana varela (Home)". Here the result is 14.
3. LEFT("Mr ana varela (Home)",14-1): The LEFT function extracts 13 characters from the left side of text string "Mr ana varela (Home)". And the final result is Mr ana varela.
Related functions
Excel RIGHT function
The Excel RIGHT function extracts a specific number of characters from the right side of the text string.
Excel LEN function
The Excel LEN function returns the number of characters in a text string.
Excel FIND function
The Excel FIND function finds a string within another string, and returns the starting position of the string inside another one.
Excel LEFT function
The Excel LEFT function extracts the given number of characters from the left side of a supplied string.
Excel TRIM function
The Excel TRIM function removes all extra spaces from text string and only keeps single spaces between words.
Excel SUBSTITUTE function
The Excel SUBSTITUTE function replaces text or characters within a text string with another text or characters.
Related formulas
Remove Extension From File Name
This tutorial provides a formula in detailed steps to help you remove extension from a file name in Excel.
Remove First N Characters From Cell
This tutorial introduces two formulas to help you easily remove the first n characters from a cell in Excel.
Remove Line Breaks From Cells In Excel
This tutorial provides three formulas to help you removing line breaks (which are occurred by pressing Alt + Enter keys in a cell) from specific cells in Excel.
Remove Text From Cell By Matching The Content
This article shows you how to use the SUBSTITUTE function to remove part of text string from specified cells by matching content.
Remove Text From A Cell Based On Specific Position
This tutorial explains how to use formulas to remove text from a cell based on specific position in Excel.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
