Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
"jgkocis" 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
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. -- jgkocis ------------------------------------------------------------------------ jgkocis's Profile: http://www.excelforum.com/member.php...o&userid=31477 View this thread: http://www.excelforum.com/showthread...hreadid=511559 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
On Sun, 12 Feb 2006 17:41:11 -0600, 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 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
"Ron Rosenfeld" 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
On Sun, 12 Feb 2006 21:57:43 -0800, "Harlan Grove" wrote:
"Ron Rosenfeld" 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
Ron Rosenfeld wrote...
On Sun, 12 Feb 2006 21:57:43 -0800, "Harlan Grove" wrote: "Ron Rosenfeld" 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
On 13 Feb 2006 09:40:08 -0800, "Harlan Grove" wrote:
Ron Rosenfeld wrote... On Sun, 12 Feb 2006 21:57:43 -0800, "Harlan Grove" wrote: "Ron Rosenfeld" 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenating an array??
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass an array to Rank | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |