Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Aug 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

2 Answers 2

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.

share|improve this answer

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))

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.