Excel Quest

March 23, 2010 8:47 AM

I have: An excel spreadsheet with a bunch of emails. I need: A self-updating text string that can be copy-pasted into an email TO: Field. I know about C1&","&D1 but is there a way for the list to be automatically updated when I or subtract an email, without specifically having to tell the formula to look in the newly added row?

I don't have: Any programming knowledge outside of crafty crafty Excel formulas.

posted by amethysts to Computers & Internet (7 answers total)

Can you give us a f'rinstance? I'm having a hard time visualizing exactly what you're trying to do.
posted by adamrice at 8:50 AM on March 23, 2010

Response by poster: Oh, ok. So I have this excel spreadsheet that looks like this:

--A1
1 [e-mail]
2 [e-mail]
3 [e-mail]


And I would like this:
[e-mail], [e-mail], [e-mail]

Such that when I edit the spreadsheet like so:
-- A
1 [e-mail]
2 [e-mail]
3 [e-mail]
4 [e-mail]

The text string automatically updates without me having to change its formula like so:
[e-mail], [e-mail], [e-mail], [e-mail]

I understand that I can make a formula that's like
A1&","A2& etc. but I would have to add the new cell to the formula every time I add a new email address.
That way I can copy/paste the text string into the To: field of an email.
posted by amethysts at 8:55 AM on March 23, 2010

Best answer: I believe you will need to write your own function, which isn't very difficult, because CONCATENATE doesn't work with a range. Here is an example.
posted by ChrisHartley at 8:57 AM on March 23, 2010

I suppose when you say you don't have any programming knowledge being told to write your own function isn't really very helpful.

This fellow wrote a concat() macro that does what you need. Downloading Excel macros from the Internet is dangerous business, you should disable macros the first time and examine the code yourself. I did that and it looked fine to me but it could be a bait-and-switch trap.

What you are doing is concatenating a range. A1:A4 is called a range and as you know stringing cells together is concatenating them, most easily done with & but also with CONCATENATE() .
posted by ChrisHartley at 9:08 AM on March 23, 2010

Response by poster: Actually I was able to puzzle it out from Chrishartley's link, and it works great!
I made the function following the instructions at MS Office's site and copy/pasting the function "Concat2" from Chrishartley's link.


Then in the cell I just put =concat2(A2:A300,";")

Thanks for your help!!
posted by amethysts at 9:13 AM on March 23, 2010

This is totally doable. I'm assuming that Column A contains a list of e-mail addresses, nothing more, nothing less (no headers, etc).

1. In cell B1, enter the following formula: =A1
2. In cell B2, enter the following formula: =IF(ISBLANK(A2),IF(ISBLANK(A3),"",B1),B1&","&A2)
3. Highlight cell B2. Click and hold the fill handle (the little black square in the lower-right corner of the cell) and drag it down several hundred rows (or further, if you anticipate more e-mail addresses than you already have).
4. The cell in column B adjacent to the last e-mail address you've entered in column A will always contain the correct string you need.
posted by julthumbscrew at 9:17 AM on March 23, 2010

Dang, beaten to the punch by something even cleverer. ;-)
posted by julthumbscrew at 9:17 AM on March 23, 2010