How do I isolate all company addresses ?

[Maverick27]

Attn all Excel Gurus

I have an Excel sheet of 500K Email Addresses.

How do isolate all company addressess eg addresses which DON'T have the following domains ?

@Yahoo ;
@gmail ;
@Hotmail ;
@Live ;
@ymail ;
@msn

Anyone care to share a Excel function or Macro to filter out the company address domains.

Below is a screenshot of the Excel list.

Thanks in Advance.

 

[Fluff]

Was the sheet with the emails the active sheet when you ran the code?
Also do you have any data that does not have an @ in it?

 

[JEC]

You could also delete them

Sub jec()
 With Range("A2", Range("A" & Rows.Count).End(xlUp))
   .Replace "*@live.*", "", xlPart
   .Replace "*@Yahoo.*", "", xlPart
   .Replace "*@Hotmail.*", "", xlPart
   .Replace "*@ymail.*", "", xlPart
   .Replace "*@msn.*", "", xlPart
   .Replace "*@Gmail.*", "", xlPart
   On Error Resume Next
   .SpecialCells(4).EntireRow.Delete
 End With
End Sub

 

[JEC]

And one non looping method with Regex

Sub jec()
 Dim ar, sp
 Set ar = Range("A1", Range("A" & Rows.Count).End(xlUp))
 
 With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(@yahoo|@gmail|@hotmail|@live|@ymail|@msn)(\.)"
    ar.AutoFilter 1, Filter(Split(.Replace(Join(Application.Transpose(ar)), "")), "@", 1), 7
 End With
End Sub

 

[BSALV]

Application.Transpose(.Value)

transpose goes wrong >65,000

 

[JEC]

Not in my version...weird. I tried with 500k rows. Did you try?

 

[Maverick27]

And one non looping method with Regex

Sub jec()
 Dim ar, sp
 Set ar = Range("A1", Range("A" & Rows.Count).End(xlUp))
 
 With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(@yahoo|@gmail|@hotmail|@live|@ymail|@msn)(\.)"
    ar.AutoFilter 1, Filter(Split(.Replace(Join(Application.Transpose(ar)), "")), "@", 1), 7
 End With
End Sub

Is this code an improvement from previous one ?

I don't see any difference in the output ?

 

[Fluff]

Not in my version...weird. I tried with 500k rows. Did you try?

I've run some tests on 300k rows of data & it definitely doesn't work, I only end up with about 34k rows visible when there should be a lot more.

 

[JEC]

But you didn't get an error?

I don't see any difference in the output ?

This method is not based on partial matches. With the filter(filter(filter method, it also finds "@live.uk.nl" because the word "@Live" is found

 

[JEC]

@Maverick27 You should consider using the replace/delete method.. that will definitely work.

 

[BSALV]

Excel 365 abonnement