Concatenating an array??

[jgkocis]

Hi-

I have a list of about 1,500 email addresses. To send an email I need to have a comma between each entry. I know I can use the formula =(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the 2 strings, but is there a formula to automatication do it for A1 - A1500???

Or is there an entirely different approach I should consider?

Thanks

[Harlan Grove]

"jgkocis" <[email protected]> wrote...
>I have a list of about 1,500 email addresses. To send an email I need
>to have a comma between each entry. I know I can use the formula
>=(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the
>2 strings, but is there a formula to automatication do it for A1 -
>A1500???
>
>Or is there an entirely different approach I should consider?

Junk mail, aka spam, is about the only thing I can think of that would
benefit from concatenating 1500-odd e-mail addresses together into a
comma-separated list. If that's your intent, then answering your question
would worsen the quality of life (or time spent on the internet) for the
rest of us.

That said, all e-mail programs I'm familiar with provide facilities to
create groups which could be used in To, cc and bcc fields. Why are you
making this so difficult trying to do it in Excel?

[jgkocis]

Actually not spam.

I am wedding photographer and went to a bridal show where all brides that wanted more information provided their email address. I received all the email addresses in an excel sheet, but to send them from yahoo i dumped them into a txt file, but have to manually add the "," unless someone can suggest a way to replace the carriage return with a comma.

[Harlan Grove]

jgkocis wrote...
>Actually not spam.
>
>I am wedding photographer and went to a bridal show where all brides
>that wanted more information provided their email address. I received
>all the email addresses in an excel sheet, but to send them from yahoo
>i dumped them into a txt file, but have to manually add the "," unless
>someone can suggest a way to replace the carriage return with a comma.

Try a Google Groups search for the mcat function. The MCONCAT function
in the MOREFUNC.XLL add-in won't help because it can't cope with so
many characters in the result string.

[Ron Rosenfeld]

On Sun, 12 Feb 2006 17:41:11 -0600, jgkocis
<[email protected]> wrote:

>
>Hi-
>
>I have a list of about 1,500 email addresses. To send an email I need
>to have a comma between each entry. I know I can use the formula
>=(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the
>2 strings, but is there a formula to automatication do it for A1 -
>A1500???
>
>Or is there an entirely different approach I should consider?
>
>Thanks

You may run into the cell character limitation using Excel unless the addresses
are quite short.

I'd use Microsoft Word for something like this.


--ron

[Harlan Grove]

"Ron Rosenfeld" <[email protected]> wrote...
....
>I'd use Microsoft Word for something like this.

Word can replace newlines?

FWLIW, and definitely off-topic, the simplest way I can think of to do this
requires the GNUWIN32 coreutils package, specifically, its tr command used
in a console pipeline command like

tr -d \r < inputfile | tr \n , > outputfile

which deletes carridge returns and replaces linefeeds with commas.

[Ron Rosenfeld]

On Sun, 12 Feb 2006 21:57:43 -0800, "Harlan Grove" <[email protected]> wrote:

>"Ron Rosenfeld" <[email protected]> wrote...
>...
>>I'd use Microsoft Word for something like this.
>
>Word can replace newlines?
>
>FWLIW, and definitely off-topic, the simplest way I can think of to do this
>requires the GNUWIN32 coreutils package, specifically, its tr command used
>in a console pipeline command like
>
>tr -d \r < inputfile | tr \n , > outputfile
>
>which deletes carridge returns and replaces linefeeds with commas.
>

I'm pretty sure it can, except that term is not used in Word. There are a
number of special codes that Word can do a find/replace.

I think that what you are calling a "newline" is what Word calls a "manual line
break" which, looking now at a Word file, seems to get coded as 0B. The
paragraph mark gets coded as 0D.

I'd guess that if Word is reading a text file, it would be translating the end
of line codes into one or the other.

I'd guess the coding in a text file would be one or the other. If the data
were saved from Excel as a CSV, and then opened in Word, the end of line
characters are "Paragraph marks" 0D.

I was also thinking that if the OP has Excel, he's likely to also have Word.

He could open the file in Word; find/replace ParagraphMark/comma (^p/,) and
save as a text file.


--ron

[Harlan Grove]

Ron Rosenfeld wrote...
>On Sun, 12 Feb 2006 21:57:43 -0800, "Harlan Grove" <[email protected]> wrote:
>>"Ron Rosenfeld" <[email protected]> wrote...
>>...
>>>I'd use Microsoft Word for something like this.
>>
>>Word can replace newlines?
....
>I'm pretty sure it can, except that term is not used in Word. There are a
>number of special codes that Word can do a find/replace.
>
>I think that what you are calling a "newline" is what Word calls a "manual line
>break" which, looking now at a Word file, seems to get coded as 0B. The
>paragraph mark gets coded as 0D.
....

I tried to do this in Word 2002, replacing ^| (what Word puts into the
Find box when I select manual line break) with a comma, but it didn't
work.

[Ron Rosenfeld]

On 13 Feb 2006 09:40:08 -0800, "Harlan Grove" <[email protected]> wrote:

>Ron Rosenfeld wrote...
>>On Sun, 12 Feb 2006 21:57:43 -0800, "Harlan Grove" <[email protected]> wrote:
>>>"Ron Rosenfeld" <[email protected]> wrote...
>>>...
>>>>I'd use Microsoft Word for something like this.
>>>
>>>Word can replace newlines?
>...
>>I'm pretty sure it can, except that term is not used in Word. There are a
>>number of special codes that Word can do a find/replace.
>>
>>I think that what you are calling a "newline" is what Word calls a "manual line
>>break" which, looking now at a Word file, seems to get coded as 0B. The
>>paragraph mark gets coded as 0D.
>...
>
>I tried to do this in Word 2002, replacing ^| (what Word puts into the
>Find box when I select manual line break) with a comma, but it didn't
>work.

When I generated the CSV file from Excel, and opened that file in Word 2002
SP3, the EOL marks were paragraph marks and not manual line breaks.

You can see what they are by Tools/Options Show All formatting marks.

The Paragraph mark looks sort of like a "P"., the manual line break looks like
the arrow on the <enter> key on a windows keyboard.

I took my comma separated file and was able to replace comma with manual line
break, and vice versa, also, with no problems.

If the above is not the issue, you could send me a copy of the file at:
(reverse the following, then make the obvious substitutions)
mocTODenilnodlefnesorTAzyx


--ron

[WCoaster]

Have you tried to export the Excel data as a *.csv file (Comma Seperated
Values)?

Simply File, Save As, choose DOS *.csv file extension and you should be good
to go. You may have to clean up the data first though.

"jgkocis" wrote:

>
> Hi-
>
> I have a list of about 1,500 email addresses. To send an email I need
> to have a comma between each entry. I know I can use the formula
> =(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the
> 2 strings, but is there a formula to automatication do it for A1 -
> A1500???
>
> Or is there an entirely different approach I should consider?
>
> Thanks
>
>
> --
> jgkocis
> ------------------------------------------------------------------------
> jgkocis's Profile: http://www.excelforum.com/member.php...o&userid=31477
> View this thread: http://www.excelforum.com/showthread...hreadid=511559
>
>