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.
This is a discussion on Need Excel formula to delete the last word in a cell within the Excel Questions forums, part of the Question Forums category; Hello, This seems so easy, but I can't get it to work correctly. If a cell or group of cells ...
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.
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.
"Don't Ruin an Apology with an Excuse"...
try chg right to "left"
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.
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.Originally Posted by jhillas
=LEFT(A1,FIND(" ",A1,LEN(A1)-6))
Started building macros in Lotus 123, then Quattro Pro and now program in Excel VBA.
Gaynard Nelson
The race is not always to the swift
But to those who keep on running
=TRIM(LEFT(A1,LEN(A1)-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))
These examples might help you:
http://www.cpearson.com/excel/FirstLast.htm
HTH,
Smitty
******** ******************** ************************************************************************>
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
=
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)
Very Handy Link...
Much appreciated.
Originally Posted by pennysaver
"Don't Ruin an Apology with an Excuse"...
Thank you all.
Yogi,
That formula is exactly what I'm looking for, once again, thank you.
Like this thread? Share it with others