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
|
|||
|
|||
Extract each item in the list
Hi.
The cell A1 contains: Here's the shopping list: pig, dog, hamburger, chocolate..., beef. Please purchase all of them. Thanks! I would like to extract each item separately to different cells, ie B1: =Display1stItem B2: =Display2ndItem .... B?: =DisplayLastItem + CropText ". Please purchase all of them. Thanks!" Reminder: There are different contents with different item list. Plus the wording of the contents and items are subject to change. So something like: MID(A1, 24, 5) is not preferred. Probably they can figure out which word to extract by pattern. In my case, when the list starts, it must start with colon (. For each item, comma (,) is used to separate each of them. So it would be if a function manages to work like the following: .... ...: pig, cow, button. ... .... Read (. the extracting starts -- Read pig -- Read (,) -- the item is extracted. Read cow -- Read (,) -- the item is extracted. Read button -- Read (.) -- the item is extracted, and stop reading after that fullstop(.) How to do? -- Additional information: - I'm using Office XP - I'm using Windows XP |
#2
|
|||
|
|||
One way of doing this is to use a search or find function to find the 1st
break and extract from the left e.g. =LEFT(A1,SEARCH(",",A1)-1). In the next column you can then extract the ramainder of the text as a "new" string to be searched e.g. =RIGHT(A1,LEN(A1)-(SEARCH(",",A1)+1)) Repeat this pattern in columns of 2. Obviously this inly really works well if all the separators are the same, otherwise you don't know when to search for a fullstop instead of a comma unless you make the nested even more complicated. A macro would be easier! "0-0 Wai Wai ^-^" wrote: Hi. The cell A1 contains: Here's the shopping list: pig, dog, hamburger, chocolate..., beef. Please purchase all of them. Thanks! I would like to extract each item separately to different cells, ie B1: =Display1stItem B2: =Display2ndItem .... B?: =DisplayLastItem + CropText ". Please purchase all of them. Thanks!" Reminder: There are different contents with different item list. Plus the wording of the contents and items are subject to change. So something like: MID(A1, 24, 5) is not preferred. Probably they can figure out which word to extract by pattern. In my case, when the list starts, it must start with colon (. For each item, comma (,) is used to separate each of them. So it would be if a function manages to work like the following: .... ...: pig, cow, button. ... .... Read (. the extracting starts -- Read pig -- Read (,) -- the item is extracted. Read cow -- Read (,) -- the item is extracted. Read button -- Read (.) -- the item is extracted, and stop reading after that fullstop(.) How to do? -- Additional information: - I'm using Office XP - I'm using Windows XP |
#3
|
|||
|
|||
Try Data-Text to columns, use comma as separator!
Stefi „0-0 Wai Wai ^-^” ezt *rta: Hi. The cell A1 contains: Here's the shopping list: pig, dog, hamburger, chocolate..., beef. Please purchase all of them. Thanks! I would like to extract each item separately to different cells, ie B1: =Display1stItem B2: =Display2ndItem .... B?: =DisplayLastItem + CropText ". Please purchase all of them. Thanks!" Reminder: There are different contents with different item list. Plus the wording of the contents and items are subject to change. So something like: MID(A1, 24, 5) is not preferred. Probably they can figure out which word to extract by pattern. In my case, when the list starts, it must start with colon (. For each item, comma (,) is used to separate each of them. So it would be if a function manages to work like the following: .... ...: pig, cow, button. ... .... Read (. the extracting starts -- Read pig -- Read (,) -- the item is extracted. Read cow -- Read (,) -- the item is extracted. Read button -- Read (.) -- the item is extracted, and stop reading after that fullstop(.) How to do? -- Additional information: - I'm using Office XP - I'm using Windows XP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
changing value of a cell by selecting an item from a drop down list | Bobby Mir | Worksheet Functions | 6 | June 8th, 2005 08:33 PM |
How select an item in a list box | jamie81 | Worksheet Functions | 1 | May 31st, 2005 09:53 AM |
Can I get rid off the blank item in drop down list? | Sam Kuo | Using Forms | 8 | April 20th, 2005 01:11 PM |
How I can extract a list from another one?? | M_Amin | Running & Setting Up Queries | 1 | April 16th, 2005 06:01 PM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |