i am fighting with the conversion of columns to rows



  • Hi Gurus,

    i am fighting with the conversion of columns to rows for the below Output. need a help on this and i have 100K records to be converted from excel.

    Main

    BCD12345678 – this is fixed length of 11 characters. and the attributes are less then 11 characters.

    Attributes are

    AXY2
    BXYS
    CXY


    Main text

    BCD12345678
    AXY2
    BXYS
    CXY
    BCF12384678
    AST4
    ASUV
    ASVG5
    ASWG
    ACF12315657
    BXY5
    CXY
    AXYA43
    DXY
    EXYS6
    YXY7

    Required output

    BCD12345678 AXY2 BXYS CXY
    BCF12384678 AST4 ASUV ASVG5 ASWG
    ACF12315657 BXY5 CXY AXYA43 DXY EXYS6 YXY7

    Thanks in Advacne



  • Assuming I’ve interpreted correctly:

    • lines that are exactly 11 alphanumeric long should be joined with any shorter lines that immediately follow, space separated.

    Regular Expression:

    • Find = “\R+(\w{1,10})$
    • Replace = “\1” (space, backslash, 1)

    (the quotes are to make spaces easier to see and copy)

    This doesn’t bother finding the 11-character lines; it basically says “if I’m a line that has a newline before me, and have 1-10 characters, followed by end of line (or end of document), then remove the newline(s) before me and replace with a space”. it has the end result of producing the output you wanted.

    If this isn’t sufficient, please provide more details. The following canned P.S. might help you understand how to provide more details.

    -----
    P.S.
    FYI: if you have further regex needs, study this FAQ and the documentation it points to. Before asking a new regex question, understand that many of us will expect you to show what data you have (exactly), what data you want (exactly), what regex you already tried (to show that you’re showing effort), why you thought that regex would work (to prove it wasn’t just something randomly typed), and what data you’re getting with an explanation of why that result is wrong. When you show that effort, you’ll see us bend over backword to get things working for you. If you need help formatting the data so that the forum doesn’t mangle it (so that it shows “exactly”, as I said earlier), see this help-with-markdown post, where @Scott-Sumner gives a great summary of how to use Markdown for this forum’s needs.



  • Thanks Perter Jones… and also thanks for quick response… it works perfectly…


Log in to reply