Email address data workflow

[goble]

Hello Excel gurus,

I have extracted emails from Outlook to CSV. Sorry if this has been asked before.

Column1 (found in A1)	Column2	Column3
To: (Address)	CC: (Address)	BCC: (Address)
email1@domainx.com
email2@domainy.com	email3@domainz.com;email4@domain1.com;	email4@domainz.com;email5@domain1.com;email6@domainz.com;email7@domain1.com (and a lot more emails here)
email8@domain5.com; email9@domain6.com	email1@domainb.com (and a lot more emails here)

Am looking for a way to have all emails in one column so I can keep unique values.

 

[veyselemre]

Hi,

Sub test()
    Dim cell As Range, c, kys()
    With CreateObject("Scripting.Dictionary")
        For Each cell In Sheets("Sheet1").UsedRange.SpecialCells(xlConstants)
            If InStr(cell.Value, "@") Then
                For Each c In Split(cell, ";")
                    If InStr(c, "@") Then .Item(Trim(c)) = ""
                Next c
            End If
        Next cell
        kys = Application.Transpose(.keys)
        Sheets("Sheet2").Range("A2").Resize(UBound(kys)).Value = kys
    End With
End Sub

 

[goble]

Thank you ! This is perfect !