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.


"Sloth" wrote:

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,FI ND(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,FI ND(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(S heet2!A1)+2,FIND(",",Sheet1!$L$3,FIND(Sheet2!A1,Sh eet1!$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(S heet2!D8)+2,FIND(",",Sheet1!$L$3,FIND(Sheet2!D8,Sh eet1!$L$3)+LEN(Sheet2!D8)+2)-(FIND(Sheet2!D8,Sheet1!$L$3)+LEN(Sheet2!D8)+2))

etc.

"Andrew" wrote:

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.