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

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