Home |
Search |
Today's Posts |
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
How select an item in a list box | Excel Worksheet Functions | |||
Need to sum up numerous columns in different worksheet into 1 | New Users to Excel | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |