Find last occurance of character in text string

[Guest]

I am working with a spreadsheet that contains product descriptions. The
descriptions have varying lengths, but all have a "suffix" code of some type
at the end of the description that I would like to eliminate. Here is an
example of the data I'm working with:

10 OZ GREEN BEANS Rfg
12 OZ CHILI WITH MEAT AND BEANS Grocery

In this example, I need to eliminate the " Rfg" in line 1, and the "
Grocery" text in line 2. My hunch is that I need to use a combination of
"FIND" and "LEFT" to basically find the last occurance of "space", then bring
back the leftmost "n" characters based on the position of that last "space".
Can someone help??

 

[Guest]

Try something like this:

With text in A1
B1: =LEFT(A1,LOOKUP(LEN(A1),FIND("
",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))-1)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Bob Umlas]

=LEFT(A1,FIND(CHAR(171),SUBSTITUTE(A1,"
",CHAR(171),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
and fill down.
Bob Umlas
Excel MVP