If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto concantenate
I am working in Office 2003.
I am trying to create a spreadsheet that I can paste data into column a and automaticaly concantenate with a ; as the seperator. The catch is that my result needs to be limited to 1000 entries, I can have multiple cells with 1000 entries. example: 1234567-1;987654;1111111-11;4561234-801; this example has 4 entries. Currently I have been getting the data in column A the in column B I enter the ;. Then I use concantenate in column C and copy it down to the end. Next I go to the 10th entry and in column D I concantenate the first 10 of column C then copy that to the bottom that gets me 10 entries per cell. Then I go to the 10th entry in column D and in column E I concantenate the first 10 entries from column D and then copy that down to the bottom that gets me to 100 entries per cell. then I go to the 10th entry in column e and in column F I concantenate the first 10 entries from column E and then copy that down to the bottom that gets me my 1000 entries per cell. I am in hope that someone knows a way to make this automated because it is taking up quite a bit of my day doing this. Thanks! -- clcnewtoaccess |
#2
|
|||
|
|||
Auto concantenate
I would use a macro. And JE McGimpsey has a macro that can be modified:
http://www.mcgimpsey.com/excel/udfs/multicat.html If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) clcnewtoaccess wrote: I am working in Office 2003. I am trying to create a spreadsheet that I can paste data into column a and automaticaly concantenate with a ; as the seperator. The catch is that my result needs to be limited to 1000 entries, I can have multiple cells with 1000 entries. example: 1234567-1;987654;1111111-11;4561234-801; this example has 4 entries. Currently I have been getting the data in column A the in column B I enter the ;. Then I use concantenate in column C and copy it down to the end. Next I go to the 10th entry and in column D I concantenate the first 10 of column C then copy that to the bottom that gets me 10 entries per cell. Then I go to the 10th entry in column D and in column E I concantenate the first 10 entries from column D and then copy that down to the bottom that gets me to 100 entries per cell. then I go to the 10th entry in column e and in column F I concantenate the first 10 entries from column E and then copy that down to the bottom that gets me my 1000 entries per cell. I am in hope that someone knows a way to make this automated because it is taking up quite a bit of my day doing this. Thanks! -- clcnewtoaccess -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|