?
Solved

Excel VBA - sort email addresses inside of a merged range

Posted on 2011-10-16
7
Medium Priority
?
238 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:AndresHernando
  • 5
  • 2
7 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 36978382
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36978415
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
 

Author Comment

by:AndresHernando
ID: 36978456
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 36978935
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36978949
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
 

Author Comment

by:AndresHernando
ID: 36979927
gowflow, nice code!  I didn't know about the SPLIT function.  Works nicely.  THANKS!!  --Andres
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36980036
Your welcome Andres anytime and tks ofr the grade
gowflow
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question