Results 1 to 8 of 8

Thread: Tranpose with Editing

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Tranpose with Editing

    How about

    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

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tranpose with Editing

    Thanks
    It works.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Tranpose with Editing

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Oct 2017
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    please advise


  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default 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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,929
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,239
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Tranpose with Editing

    Quote Originally Posted by Fluff View Post
    =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 by Rick Rothstein; Sep 6th, 2019 at 10:28 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •