Find last occurance of character in text string

J

JDay01

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

Ron Coderre

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
 
B

Bob Umlas

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top