Tranpose with Editing

[sahaider]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 511"]
<colgroup><col width="536" style="width: 402pt; mso-width-source: userset; mso-width-alt: 19602;"> <col width="145" style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;"> <tbody>[TR]
[TD="width: 536, bgcolor: transparent, align: left"]Site list[/TD]
[TD="width: 145, bgcolor: transparent, align: left"]Site list[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]44155/44215/44240/44246/44289/44336/44375/44387/44434/44443/44506/44513[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hello,

I have list of numbers in a row which are separated by / between them, I would like to convert this list in a column without the / in between them.

as seen above in the Site list column on the right I would like to have the end result in a single column but multiple rows where each row will hold a 5 digit number.

Thanks

Syed

 

[Fluff]

How about

Book1
	A	B
2	44155/44215/44240/44246/44289/44336/44375/44387/44434/44443/44506/44513	44155
3		44215
4		44240
5		44246
6		44289
7		44336
8		44375
9		44387
10		44434
11		44443
12		44506
13		44513
Sheet3

Cell Formulas
Range	Formula
B2	=MID($A$2,(ROW(A1)-1)*6+1,5)*1

 

[sahaider]

Thanks
It works.

 

[Fluff]

You're welcome & thanks for the feedback

 

[sg2209]

this is very impressive, i am just reviewing lots of post and trying to learn as much as i can by @ Fluff- if you don't mind can you please us understand why you use 6+1,5)*1, so should i post a new query.
please advise

 

[Fluff]

It's to get the start point for the Mid function.
For the first row the formula is in
(ROW(A1)-1)*6+1 becomes (1-1)*6+1 = 0*6+1= 1 so the mid is the MID(A2,1,5)
for the second row you get
(2-1)*6+1 =1*6+1=7 so you get MID(A2,7,5)

The final *1 simply converts the text value into a number.

 

[alansidman]

An alternative method is to use Power Query. Bring your data into PQ. Split the column employing "/" as your delimiter. Then unpivot your columns. Delete the unnecessary column. Here is the Mcode to accomplish this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}, {"Column1.3", Int64.Type}, {"Column1.4", Int64.Type}, {"Column1.5", Int64.Type}, {"Column1.6", Int64.Type}, {"Column1.7", Int64.Type}, {"Column1.8", Int64.Type}, {"Column1.9", Int64.Type}, {"Column1.10", Int64.Type}, {"Column1.11", Int64.Type}, {"Column1.12", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

 

[Rick Rothstein]

=MID($A$2,(ROW(A1)-1)*6+1,5)*1

If you complete the highlighted mathematical calculation, the above formula "simplifies" to this..

=MID($A$2,6*ROW(A1)-5,5)*1