Extract each item in the list

[0-0 Wai Wai ^-^]

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?

 

[Pallet1210A]

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!

 

[Stefi]

Try Data->Text to columns, use comma as separator!
Stefi


„0-0 Wai Wai ^-^†ezt írta: