# Excel function to remove last two words from a cell

Using operators for simple arithmetic 4m 44s

creating new database 3m 2s

These are the two values in two Excel cells.

I want to remove the last two words, that is, 4m 44s from every cell so that the output should be:

Using operators for simple arithmetic

creating new database

``````=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))
``````

but this function is removing only last the word. How do I remove the last two words from each cell?

-

## migrated from stackoverflow.comAug 28 '12 at 12:57

This question came from our site for professional and enthusiast programmers.

I just looked at your formula closely and realized it's the same as the one I posted below. Then, I tested your formula and realized it works (with a trailing space left behind). Could you post some example text on which your formula fails? – Excellll Aug 28 '12 at 13:35

Here's a formula that will do it. It's a little long, but it will do the trick:

``````=LEFT(A1,FIND("@@REPLACE@@",SUBSTITUTE(TRIM(A1)," ","@@REPLACE@@",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))-1)
``````

The formula works by using the `SUBSTITUTE` function to replace the next-to-last space with some obscure replace text that isn't likely to be found anywhere else in the text ("@@REPLACE@@" in the example). Then the `LEFT` function can return everything to the left of the replace text. The reason for doing it this way is because `FIND` does not allow you to specify an instance of a space, but rather a starting position, which is not useful in this case. `SUBSTITUTE` however lets you specify the instance number of the space, so that is why it is used.

Expect an error to be returned for text with two or fewer words.

-

Probably you have a trailing spaces in your text because as Excellll says it should work otherwise. Try adding in some `TRIM` functions to remove trailing spaces (`TRIM` will also remove any leading spaces and make any double internal spaces into single spaces.....but I assume that won't be a problem?)

`=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1)))`

or here's another (shorter) way,

`=TRIM(LEFT(A1,FIND(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",9)),18)),A1)-1))`

-