$20 USD - Copy data from many columns into one cell, into specific positions.

  • Hi All,


    Here's my issue today. I've got a spreadsheet template that I receive where I need to extract data from 6 different columns and have the results in one cell in a new column. I've tried using concatenation with spacing, but couldn't some up with a universal formula that fits my needs.
    The data in each column has to start in a particular position in the new cell. Hopefully the attached spreadsheet explains what I need more clearly. Not sure if this can be done with a formula, VBA, or maybe not at all, but I'm spinning my wheels and thought I'd ask for the experts help.
    Offering up $20 but if a more complex solution/programming is needed, that is negotiable.


    Sent 10% to Oz. Transaction ID 9DV743169J592954U


    Thanks for taking a look! :)

  • When do you need it?

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Grand... will have it for you tomorrow at some point. Just going to bed :-)

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Hiya, this seems to do the trick for a formula to hit the markings you mentioned:
    =$A3 & REPT(" ",9-LEN($A3)) & $B3 & REPT(" ", 14-LEN($B3)) & $C3 & REPT(" ",14-LEN($C3)) & $D3 & REPT(" ", 43-LEN($D3)) & $K3 & REPT(" ", 4-LEN($K3)) & $L3


    If you for some reason you think that the values will be WIDER (number of characters) than you are expecting in the given column, for example someone entered 10 characters into Column A, instead of 9, then the formula will return #Value, which seems like it would be correct (i.e. something you want to manually investigate). However - if you want to allow "bad incoming data" then you have two choices as follows:
    1. Truncate the value in the column to the specified or allowable data format width - this formula will do that, but it might be dangerous for things like Bid Price/Ask price getting truncated. This one is a little messy.
    =LEFT($A3,9) & REPT(" ",MAX(0,9-LEN($A3))) & LEFT($B3,14) & REPT(" ", MAX(0,14-LEN($B3))) & LEFT($C3,14) & REPT(" ",MAX(0,14-LEN($C3))) & LEFT($D3,43) & REPT(" ", MAX(0,43-LEN($D3))) & LEFT($K3,4) & REPT(" ",MAX(0, 4-LEN($K3))) & LEFT($L3,4)


    2. Accept / allow more characters than are allowed in the data format and shove everything else to the right, then this formula will allow this. This might "break" your downstream use of the resulting cell data, as it may break your data format rules
    =$A3 & REPT(" ",MAX(0,9-LEN($A3))) & $B3 & REPT(" ", MAX(0,14-LEN($B3))) & $C3 & REPT(" ", MAX(0,14-LEN($C3))) & $D3 & REPT(" ", MAX(0,43-LEN($D3))) & $K3 & REPT(" ", MAX(0,4-LEN($K3))) & $L3


    The VBA implementation and equivalent of the very first formula above can be done in a custom function that you call from Excel (assumes the data is in good order, and ignores character width issues I mention above))



    You would call it thus from a cell, pointing at the range of cells that needs to be concatenated:
    =My_Concat(A3:L3)


    If needed I can implement into a traditional Macro too that you call from the Macro menu, but to be honest, the Function is far cleaner


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Received - thank you so much for the quick payment. Please shout back if any problems and I will look into it.


    Gerard

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________