Tranpose with Editing

sahaider

New Member
Joined
May 30, 2014
Messages
35
Site listSite list
44155/44215/44240/44246/44289/44336/44375/44387/44434/44443/44506/44513
<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> </tbody>


<tbody>
</tbody>
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
How about


Book1
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
Cell Formulas
RangeFormula
B2=MID($A$2,(ROW(A1)-1)*6+1,5)*1
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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

 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
=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
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top