Need Excel formula to delete the last word in a cell

[jhillas]

Hello,

This seems so easy, but I can't get it to work correctly.
If a cell or group of cells contains a product description, and the last word is undesired in all the cells, how would we go about this? I've found this formula to delete the first word:

=RIGHT(A1,LEN(A1)-FIND(" ", A1,1))

before:
This is a test.

after
is a test.

What is desired is:
This is a

Thank you.

 

[santeria]

Pretty neat reference

http://www.mrexcel.com/board2/viewtopic.php?t=49776&highlight=delete+last+word+cell

Should be cross-applicable with the code there, so you can get it to do what you want.

 

[2rrs]

try chg right to "left"

 

[jhillas]

Thank you santeria, I'll check it out.
2rrs, I tried "left" and it works, but not correctly, for example.

Cell A1 contains:

This is a very long test C340409

Using "left", will return the results:

This is a very long test C34

The formula only deletes 5 characters back.

 

[gaynard_nelson]

Hello,

I've found this formula to delete the first word:

=RIGHT(A1,LEN(A1)-FIND(" ", A1,1))

This is a

Thank you.

This formula will work if the last space is in the last six characters of the entry. If the last space is more than six characters from the end then you would have to modify this formula. Maybe it will give you some idea how to proceed.

=LEFT(A1,FIND(" ",A1,LEN(A1)-6))

 

[Aladin Akyurek]

=TRIM(LEFT(A1,LEN(A1)-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

 

[Smitty]

These examples might help you:

http://www.cpearson.com/excel/FirstLast.htm

HTH,

Smitty

 

[Yogi Anand]

y031211h1.xls
	A	B	C	D
1	Thisisatest.	Thisisa
Sheet8

The formula in cell B1 is ...

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

 

[santeria]

Very Handy Link...

Much appreciated.

These examples might help you:

http://www.cpearson.com/excel/FirstLast.htm

HTH,

Smitty

 

[jhillas]

Thank you all.

Yogi,

That formula is exactly what I'm looking for, once again, thank you.