Prev: what row do I start an address for a window envelope?
Next: Combo Box
From: clcnewtoaccess on 22 Apr 2010 07:48
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
From: Dave Peterson on 22 Apr 2010 08:09
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 here:
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
 | 
Pages: 1
Prev: what row do I start an address for a window envelope?
Next: Combo Box