On Tue, 10 Jan 2006 06:41:03 -0800, 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.

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?)+",Sub String)

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

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


--ron