Tranpose with Editing

sahaider

New Member
Joined
May 30, 2014
Messages
12
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">44155/44215/44240/44246/44289/44336/44375/44387/44434/44443/44506/44513</td><td style="text-align: right;;">44155</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">44215</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">44240</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">44246</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">44289</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">44336</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">44375</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">44387</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">44434</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">44443</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">44506</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">44513</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=MID(<font color="Blue">$A$2,(<font color="Red">ROW(<font color="Green">A1</font>)-1</font>)*6+1,5</font>)*1</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

sg2209

Board Regular
Joined
Oct 27, 2017
Messages
71
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,575
Office Version
2019
Platform
Windows
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"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,924
Office Version
2010
Platform
Windows
=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:

Watch MrExcel Video

Forum statistics

Threads
1,099,364
Messages
5,468,189
Members
406,570
Latest member
Ktvu2006

This Week's Hot Topics

Top