Cut list into pieces that are marked by /

[Sorttaaja2]

How to cut list into pieces that are marked by / ?


so that 1/2/3/4


would become


1/
2/
3/
4/






By doing a macro? so that it would cut after / and paste into new row? and repeat until the last / ?




Thankyou

 

[sandy666]

with your example you can try PowerQuery aka Get&Transform

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Added Suffix" = Table.TransformColumns(#"Split Column by Delimiter", {{"Column1", each _ & "/", type text}})
in
    #"Added Suffix"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1/2/3/4[/td][td][/td][td=bgcolor:#E2EFDA]1/[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]2/[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]3/[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]4/[/td][/tr]
[/table]

 

[jtakw]

Hi,

Another way, with formula:

Book1
	A	B
1	1/2/3/4	1/
2		2/
3		3/
4		4/
Sheet335

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(A$1&"/","/","/"&REPT(" ",100)),ROWS(B$1:B1)*100-99,100))

Formula copied down as far as needed.