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

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

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