AndresHernando
asked on
Excel VBA - sort email addresses inside of a merged range
I have multiple email addresses inside a named merged range rEmailDistrib. The email addresses are separated by semicolon, or comma, or colon, or space(s). I need to manipulate thus:
1) Leave all email addresses inside the merged range
2) Sort A-Z, one email address per line inside the merged range
3) Put semicolon at the end of each email address (get rid of all spaces)
For example, original contents of rEmailDistrib is:
abc4@mail.com,abc2@mail.or
New content of rEmailDistrib would be...
abc1@mail.mil;
abc2@mail.org;
abc3@mail.swa.mil;
abc4@mail.com;
Maybe the way to approach is by starting out with a "helper" worksheet cell containing "=Clean(rEmailDistrib)"
Thanks, --Andres
1) Leave all email addresses inside the merged range
2) Sort A-Z, one email address per line inside the merged range
3) Put semicolon at the end of each email address (get rid of all spaces)
For example, original contents of rEmailDistrib is:
abc4@mail.com,abc2@mail.or
New content of rEmailDistrib would be...
abc1@mail.mil;
abc2@mail.org;
abc3@mail.swa.mil;
abc4@mail.com;
Maybe the way to approach is by starting out with a "helper" worksheet cell containing "=Clean(rEmailDistrib)"
Thanks, --Andres
1) I read your post again and you mention 'merged name range' so I assume it is 1 cell (merged) that you maybe enlarged and it contain all the emails that you need sorted/cleaned
Pls confirm my understanding correct.
2) When they are cleaned where do you want the emails to show in hte same named range each one on 1 line ?
gowflow
Pls confirm my understanding correct.
2) When they are cleaned where do you want the emails to show in hte same named range each one on 1 line ?
gowflow
ASKER
Hi gowflow,
Yes, one single cell (merged range really) has all the text that needs to be cleaned and sorted.
I have figured out how to clean the text and now have a long string with no spaces, with each email address separated by a semicolon ";"
Now I need to sort them in the same cell and have a line feed at the end of each so that inside the cell the contents will be
abc1@mail.mil;
abc2@mail.org;
abc3@mail.swa.mil;
abc4@mail.com;
.
.
Thanks, --Andres
Yes, one single cell (merged range really) has all the text that needs to be cleaned and sorted.
I have figured out how to clean the text and now have a long string with no spaces, with each email address separated by a semicolon ";"
Now I need to sort them in the same cell and have a line feed at the end of each so that inside the cell the contents will be
abc1@mail.mil;
abc2@mail.org;
abc3@mail.swa.mil;
abc4@mail.com;
.
.
Thanks, --Andres
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In addition, if your named range is diffrent just change in this
CleanEmails ActiveSheet.Name & "!rEmailDistrib"
the rEmailDistrib by hte new named range where the emails exist.
gowflow
CleanEmails ActiveSheet.Name & "!rEmailDistrib"
the rEmailDistrib by hte new named range where the emails exist.
gowflow
ASKER
gowflow, nice code! I didn't know about the SPLIT function. Works nicely. THANKS!! --Andres
Your welcome Andres anytime and tks ofr the grade
gowflow
gowflow
gowlfow