Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text strings along commas.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text strings along commas.
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text strings along commas.
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text strings along commas.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
splitting text from one cell | Excel Discussion (Misc queries) | |||
Separating Text strings. | Excel Discussion (Misc queries) | |||
space between text strings with concatenate | Excel Discussion (Misc queries) | |||
Compare cells/columns and highlight matching text strings | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |