Need Excel formula to delete the last word in a cell

Thanks:  0
Likes:  0

Thread: Need Excel formula to delete the last word in a cell

1. Need Excel formula to delete the last word in a cell

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.

2. Re: Need Excel formula to delete the last word in a cell

Pretty neat reference

http://www.mrexcel.com/board2/viewto...last+word+cell

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

3. Re: Need Excel formula to delete the last word in a cell

try chg right to "left"

4. 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.

5. Re: Need Excel formula to delete the last word in a cell

Originally Posted by jhillas
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))

6. Re: Need Excel formula to delete the last word in a cell

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

7. Re: Need Excel formula to delete the last word in a cell

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

HTH,

Smitty

8. Re: Need Excel formula to delete the last word in a cell

******** ******************** ************************************************************************>
 Microsoft Excel - y031211h1.xls ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B1 =

A
B
C
D
1
This is a test.This is a
 Sheet8

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The formula in cell B1 is ...

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

9. Re: Need Excel formula to delete the last word in a cell

Much appreciated.

Originally Posted by pennysaver

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

HTH,

Smitty

10.
Thank you all.

Yogi,

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

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•