# Thread: Tranpose with Editing Thanks: 0 Likes:  1 Post #5338234 (1)

1. ## Tranpose with Editing

 Site list Site list 44155/44215/44240/44246/44289/44336/44375/44387/44434/44443/44506/44513
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

2. ## Re: Tranpose with Editing

AB
244155/44215/44240/44246/44289/44336/44375/44387/44434/44443/44506/4451344155
344215
444240
544246
644289
744336
844375
944387
1044434
1144443
1244506
1344513

Sheet3

Worksheet Formulas
CellFormula
B2=MID(\$A\$2,(ROW(A1)-1)*6+1,5)*1

Thanks
It works.

4. ## Re: Tranpose with Editing

You're welcome & thanks for the feedback

5. ## Re: Tranpose with Editing

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.

6. ## Re: Tranpose with Editing

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.

7. ## Re: Tranpose with Editing

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.

Code:
```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"```

8. ## Re: Tranpose with Editing

Originally Posted by Fluff
=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