Splitting text strings along commas.

[Guest]

Dear all,
I need to split (along comma) a lot of text strings of different lenths into
pieces. Those short pieces of the text string shall then each be visible in
its own cell on another worksheet. One single string can contain up to 9
commas. For example:

The cell {'Worksheet1'!L3} contains the following text string:
Chair, Airplane, House, Net, Appel Tree, Bird, Desktop, Fly, Mouse.

Should be splitted and visible as followed:
{'Worksheet2'!A1} Chair
{'Worksheet2'!D8} Airplane
{'Worksheet2'!D9} House
{'Worksheet3'!B12} Net
{'Worksheet3'!A8} Apple Tree
{'Worksheet5'!Z80} Bird
etc.

Does anyone know a formula to achieve that?

Many thanks and best regards.

 

[Ron Rosenfeld]

Dear all,
I need to split (along comma) a lot of text strings of different lenths into
pieces. Those short pieces of the text string shall then each be visible in
its own cell on another worksheet. One single string can contain up to 9
commas. For example:

The cell {'Worksheet1'!L3} contains the following text string:
Chair, Airplane, House, Net, Appel Tree, Bird, Desktop, Fly, Mouse.

Should be splitted and visible as followed:
{'Worksheet2'!A1} Chair
{'Worksheet2'!D8} Airplane
{'Worksheet2'!D9} House
{'Worksheet3'!B12} Net
{'Worksheet3'!A8} Apple Tree
{'Worksheet5'!Z80} Bird
etc.

Does anyone know a formula to achieve that?

Many thanks and best regards.

How do you know into which cell the segment will go?

In any event, this can be done with a combination of complex SUBSTITUTE, FIND,
MID, LEFT, RIGHT and error functions, or it can be done more simply by using
"regular expressions".

To use the latter, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=REGEX.MID(TRIM('Worksheet1'!$L$3),"(\w+\s?)+",SubString)

where SubString is the text segment you want to pull out.

Put that formula in the appropriate cells on the appropriate worksheets.


--ron

 

[Guest]

To make things easier I did them all in the same worksheet. Just change the
reference accordingly. I placed the list in A1 and the formulas in A3-A11.
I also had to put a comma at the very end, otherwise you get an error with
the last word.

A1: Chair, Airplane, House, Net, Appel Tree, Bird, Desktop, Fly, Mouse,

A3: =LEFT(Sheet1!A1,FIND(",",Sheet1!A1)-1)
A4:
=MID($A$1,FIND(A3,$A$1)+LEN(A3)+2,FIND(",",$A$1,FIND(A3,$A$1)+LEN(A3)+2)-(FIND(A3,$A$1)+LEN(A3)+2))
A5:
=MID($A$1,FIND(A4,$A$1)+LEN(A4)+2,FIND(",",$A$1,FIND(A4,$A$1)+LEN(A4)+2)-(FIND(A4,$A$1)+LEN(A4)+2))
..
..
..

Copy A4 down to A11. You can change the references by simply cut and
pasting (absolute references change when cut and pasteing) the cells to where
they need to go. It will look something like this...

{'Worksheet2'!A1} =LEFT(Sheet1!L3,FIND(",",Sheet1!L3)-1)

{'Worksheet2'!D8}
=MID(Sheet1!$L$3,FIND(Sheet2!A1,Sheet1!$L$3)+LEN(Sheet2!A1)+2,FIND(",",Sheet1!$L$3,FIND(Sheet2!A1,Sheet1!$L$3)+LEN(Sheet2!A1)+2)-(FIND(Sheet2!A1,Sheet1!$L$3)+LEN(Sheet2!A1)+2))

{'Worksheet2'!D9}
=MID(Sheet1!$L$3,FIND(Sheet2!D8,Sheet1!$L$3)+LEN(Sheet2!D8)+2,FIND(",",Sheet1!$L$3,FIND(Sheet2!D8,Sheet1!$L$3)+LEN(Sheet2!D8)+2)-(FIND(Sheet2!D8,Sheet1!$L$3)+LEN(Sheet2!D8)+2))

etc.

 

[Guest]

If you don't like adding a comma at the end you can change the last formula
to this

=MID(A1,FIND(A10,A1)+LEN(A10)+2,LEN(A1)-(FIND(A10,A1)+LEN(A10)+1))

A10 Being the word "Fly" and A1 being the original list.