How To Remove Words In Excel

Excel formula to delete the first or last word from a cell.

Y'all can copy and paste the formulas beneath for a quick fix and also learn how they work, if yous're interested.

Sections:

Remove the Start Word from a Cell

Remove the Final Word from a Prison cell

More Versatile Formulas - Prevents Errors

Notes

Remove the First Give-and-take from a Cell

          =Right(A1,LEN(A1)-Discover(" ",A1))

Result:

This assumes the text is in prison cell A1, change every bit needed for your spreadsheet.

Non-Space Characters

If your words use dashes or another separator, just supplant " " with whatever separator is used. If a dash is used, alter that to "-" in your formula.

Caption

In that location are three functions at work hither: the Right(), LEN(), and Notice() functions.

The Correct role is what gets the text from the right of the cell. This is needed because we are extracting all of the text except the showtime word; that means nosotros need to go everything that is to the right of the starting time word in the cell.

The LEN and Observe functions are used to tell the RIGHT function how many characters on the right of the cell nosotros want to get. The RIGHT role only extracts then many characters from the right of the cell.

To go everything except the first discussion, nosotros need to find the infinite that separates the showtime word and the rest of the text. Use the Discover function, searching for a space " " to get the number of characters from the left of the cell until the first space. In the example above this returns five. That is the number of characters that nosotros want to remove from the prison cell.

To become the number that we want to keep, we need to use the LEN part to count all of the characters in the cell. The outcome of the LEN function is 12 because there are 12 characters in the jail cell.

In the formula higher up, we subtract the effect of FIND from that of LEN, which is 12-5, or 7.

This tells the RIGHT function to get 7 characters from the right of the prison cell, which finer removes the first word from the prison cell.

Remove the Concluding Word from a Cell

          =LEFT(A1,Detect("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-ane)

Result:

This assumes the text is in cell A1, change as needed for your spreadsheet.

Annotation: if your cells might have the ~ character in them, alter that role of the formula to a character that won't be present in the cells.

Non-Space Characters

If your words use dashes or some other separator, just replace " " with whatever separator is used. If a nuance is used, change that to "-" in your formula.

Caption

This formula uses four functions: LEFT(), LEN(), FIND(), SUBSTITUTE().

This tin be a bit confusing but you don't need to read this section in order to utilise the formula.

In order to remove the last word from the cell, we demand to extract all of the other words to a new prison cell and this new cell will and then non contain the final give-and-take. Basically, we are getting all of the words that are to the left of the concluding word, so we employ the LEFT function.

To do this, we need to tell the LEFT role how many characters from the left of the cell that we want in the new cell, and that number tin vary. The tricky function of the formula is what gets us this number.

          Find("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

This piece of the formula SUBSTITUTE(A1," ","") removes all spaces in the cell and then counts how many characters are left using the LEN function LEN(SUBSTITUTE(A1," ","")). From the example above, the result of this is 10.

Information technology then counts how many characters are in the prison cell when the spaces are left in it: LEN(A1). The result is 12.

Then the formula subtracts the two above numbers to become ii in this case. This number is the total number of spaces that are in the cell and this tells the other SUBSTITUTE role which space to supersede; we want to replace the last infinite and then we can find it more hands.

Using the number we but got, the SUBSTITUTE role is used again to supervene upon the last space in the cell with ~ and then the Notice part is used to observe that ~ and and then the formula knows how many characters from the left of the jail cell the last infinite is.

The formula then subtracts 1 from that number since we don't desire to include the last space in the new cell.

Now, we take the full number of characters from the left of the cell up to the infinite that comes before the last word in the cell and we can use the LEFT function to return everything except the final give-and-take.

If yous are withal a piddling confused, check out our tutorial on the SUBSTITUTE function in Excel

More Versatile Formulas - Prevents Errors

If you employ 1 of the above formulas on a list and any of the cells in that listing contains a single word, an error volition appear.

To avert any errors, we can use a simple IFERROR() function.

Formula 1:

          =IFERROR(Right(A1,LEN(A1)-FIND(" ",A1)),A1)

Formula two:

          =IFERROR(LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),A1)

This says that if there is an error, output the contents of the original cell. Errors similar this usually happen if there is merely one word in the cell, then this would output that single give-and-take.

Read our tutorial to larn more about the IFERROR function in Excel and suppressing errors in general.

Notes

These formulas tin can be confusing. I recommend simply copy/pasting them into your spreadsheet and changing the prison cell references as needed.

To make the new range of values text instead of formulas follow this tutorial for converting formulas to values.

Make sure to download the sample file attached to this tutorial to get these examples in Excel.