Find last occurance of character in text string

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

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

"JDay01" <[email protected]> wrote in message
news:[email protected]...
> 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??

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


"JDay01" wrote:

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