If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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?? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking text boxes | Volunteer Mom | Publisher | 7 | November 12th, 2008 01:29 AM |
Edit/Add record in form from cmdButton | doodle | General Discussion | 3 | December 28th, 2005 03:06 AM |
Newbie Looking for Help | Little Penny | Using Forms | 6 | December 27th, 2005 08:33 PM |
Word applies direct format on File open | Uriel | General Discussion | 16 | November 27th, 2005 07:22 PM |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |