Concatenate email addresses into 1 cell

**Launchnet** · 05-29-2009, 09:20 AM

I have a list of email addresses. They are in column A.
The 1st email address is in cell A5 and goes down until a blank cell is reached.

QUESTION: Is there a limit on how many email addresses can be contantinated together ? I can limit the number if necessary.

The contantinated list should be placed into cell A1.

Can someone tell me how this can be done with a macro ?

**DonkeyOte** · 05-29-2009, 09:24 AM

Something like ?

Please Login or Register  to view this content.

Have assumed you want to split addresses by semi colon (alter Join accordingly)

Depending on your version the length of the permitted string will vary.

**Launchnet** · 05-29-2009, 09:42 AM

Good Morning . . . DonkeyOte

Thanks, it works perfect.

Question: Can you better describe what you mean by "Limited by Version". This application is used in different versions of Excel and Explorer.

Could I set a maximum that all versions would handle?

**Kenneth Hobson** · 05-29-2009, 09:59 AM

You could set a minimum but you would need to decide what is the lowest version and get the limits. For 2003 see: http://office.microsoft.com/en-us/ex...992911033.aspx

**DonkeyOte** · 05-29-2009, 10:00 AM

Versions of XL have differing restrictions in terms of the number of characters that a given cell can hold / display.... I can't recall them off hand... I think it used to be 32000 in 2003 with only 1024 displayed - if anyone has the limits do please post (edit: see Kenneth's link for 2003)... 2007 appears to display up to 8221 ... I should know this stuff better :oops:

On an aside you could use as a function as opposed to subroutine

Please Login or Register  to view this content.

And then in A1

=ConcatMail(A5:INDEX(A:A,MATCH(REPT("Z",255),A:A)),";")

**Launchnet** · 05-29-2009, 10:33 AM

Thanks to both of you. I've tested 300 email addresses and it works fine. There is no way that we will ever come close to this.

I do have one more question.

IN REVERSE: As an example, I have a list already contantinated in cell A1. How can I un-contantinate this list and place the individual email addresses in column A starting at cell A5 and down ?

**DonkeyOte** · 05-29-2009, 10:38 AM

Similar to before as you'd expect

Please Login or Register  to view this content.

(you might want to add code to clear the range if already exists?)

**Launchnet** · 05-29-2009, 11:18 AM

Works perfect except . . . Unexpected by me, the list no longer is a link. This is not a problem. It just looks different when it's not displayed as a link. The difference being that from time to time addresses will be typed into the list and they will show as a link.

Not important at all, but if anyone thinks of a way to show the results as links it would be interesting.