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.org   abc1@mail.mil; abc3@mail.swa.mil: abc5@mail.com

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
AndresHernandoAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
Here it is
This file is a test file with email addresses pls click on Clean email Address and check the reuslt.

the code below is the sub that makes the whole work
and you can call it as follows:
CleanEmails ActiveSheet.Name & "!rEmailDistrib"
gowlfow
Sub CleanEmails(NamedRange As String)
On Error GoTo ErrHandler

Dim EBefore, ECleaned, ESorted
Dim I As Long, J As Long, CriLoc As Long
Dim Crit As String
Dim CelRef As String
Dim Emails, SubEmails
Dim nm


For Each nm In ActiveWorkbook.Names
        If nm.Name = NamedRange And Not IsError(nm.RefersToRange.Address) Then
            CelRef = nm.RefersToRange.Address
            Exit For
        End If
Next nm

EBefore = Range(CelRef)
ECleaned = ""

'Clean Emails
Emails = Split(EBefore, " ")
For I = 0 To UBound(Emails)
    TmpEmail = Trim(Emails(I))
        If TmpEmail <> "" Then
            Crit = ";"
            If InStr(1, TmpEmail, Crit, vbTextCompare) > 0 Then
                SubEmails = Split(TmpEmail, Crit)
                For J = 0 To UBound(SubEmails)
                    If SubEmails(J) <> "" Then ECleaned = ECleaned & SubEmails(J) & " "
                Next J
            End If
                
            Crit = ","
            If InStr(1, TmpEmail, Crit, vbTextCompare) > 0 Then
                SubEmails = Split(TmpEmail, Crit)
                For J = 0 To UBound(SubEmails)
                    If SubEmails(J) <> "" Then ECleaned = ECleaned & SubEmails(J) & " "
                Next J
            End If
            
            Crit = ":"
            If InStr(1, TmpEmail, Crit, vbTextCompare) > 0 Then
                SubEmails = Split(TmpEmail, Crit)
                For J = 0 To UBound(SubEmails)
                    If SubEmails(J) <> "" Then ECleaned = ECleaned & SubEmails(J) & " "
                Next J
            End If
            
            If InStr(1, TmpEmail, ";", vbTextCompare) = 0 And InStr(1, TmpEmail, ",", vbTextCompare) = 0 And InStr(1, TmpEmail, ":", vbTextCompare) = 0 And TmpEmail <> "" Then
                ECleaned = ECleaned & TmpEmail & Chr(10)
            End If
        End If
Next I

Emails = Split(ECleaned, Chr(10))
For J = 0 To UBound(Emails)
    Range("IV" & J + 1) = Emails(J)
Next J
Range("IV1:IV" & UBound(Emails)).Sort Key1:=Range("IV1"), order1:=xlAscending, header:=xlGuess

ESorted = ""
For J = 1 To UBound(Emails)
ESorted = ESorted & Range("IV" & J) & ";" & Chr(10)
Next J

Range("IV:IV").Delete
Range(CelRef) = ESorted

MsgBox ("Range '" & NamedRange & "' Cleaned and sorted successfully.")

Exit Sub

ErrHandler:
MsgBox ("Error " & Error(Err))


End Sub

Open in new window

CleanSortEmailAddress.xls
0
 
gowflowCommented:
you range rEmailDistrib contains how many rows/cells ? what is its address ? do you have 1 email per cell or some cells have many emails ?
gowlfow
0
 
gowflowCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
AndresHernandoAuthor Commented:
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
0
 
gowflowCommented:
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
0
 
AndresHernandoAuthor Commented:
gowflow, nice code!  I didn't know about the SPLIT function.  Works nicely.  THANKS!!  --Andres
0
 
gowflowCommented:
Your welcome Andres anytime and tks ofr the grade
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.