Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last occurance of character in text string
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?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last occurance of character in text string
=LEFT(A1,FIND(CHAR(171),SUBSTITUTE(A1,"
",CHAR(171),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) and fill down. Bob Umlas Excel MVP "JDay01" wrote in message ... 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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - Separating numbers appearing on the right of a text string | Excel Worksheet Functions | |||
Trying to match a text string to a data table, any suggestions? | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Excel - Find & Replace text in a string | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |