How do I isolate all company addresses ?

Maverick27

Active Member
Joined
Sep 23, 2010
Messages
283
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.

excel.jpg
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
Do you just want the domains such as unchr & yu?
 

BSALV

Well-known Member
Joined
Oct 31, 2010
Messages
1,456
Office Version
  1. 365
  2. 2013
  3. 2007
what is your excel version ?
 

DeficientOptimism

New Member
Joined
Jan 19, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Excel Formula:
 IF(ISNUMBER(SEARCH("@Yahoo",A2)),"Needs to be excluded",IF(ISNUMBER(SEARCH("@Gmail",A2)),"Needs to be excluded",IF(ISNUMBER(SEARCH("@Hotmail",A2)),"Needs to be excluded",IF(ISNUMBER(SEARCH("@Live",A2)),"Needs to be excluded",IF(ISNUMBER(SEARCH("@ymail",A2)),"Needs to be excluded",IF(ISNUMBER(SEARCH("@msn",A2)),"Needs to be excluded","Include"))))))

I think this should work but may potentially be very slow calculating your sheet given the size of the workbook.

There is probably a better approach to this.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

MS Professional Plus 2013

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,394
Office Version
  1. 2013
Platform
  1. Windows
How about
VBA Code:
Sub test()
    Dim a, b
    Dim i, k
    k = 1
    a = Sheets("sheet1").Cells(2, 1).Resize(Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row - 1)
    ReDim b(1 To UBound(a))
    With CreateObject("VBScript.RegExp")
        .Pattern = "@yahoo|@gmail|@hotmail|@live|@ymail|@msn"
        .IgnoreCase = True
        For i = 1 To UBound(a)
            If .test(a(i, 1)) Then
                b(k) = a(i, 1)
                k = k + 1
            End If
        Next
    End With
    Sheets("sheet1").Cells(2, 2).Resize(k - 1) = Application.Transpose(b)
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Maverick()
   Dim Ary As Variant, Nary As Variant, Sp As Variant
   Dim Lst As String
   Dim r As Long, nr As Long
   
   Lst = "|Yahoo|gmail|Hotmail|Live|ymail|msn"
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         Sp = Split(Split(Ary(r, 1), "@")(1), ".")(0)
         If InStr(1, Lst, "|" & Sp & "|", vbTextCompare) = 0 Then
            If Not .Exists(Sp) Then
               nr = nr + 1
               Nary(nr, 1) = Sp
               .Add Sp, Nothing
            End If
         End If
      Next r
   End With
   Range("B2").Resize(nr).Value = nr
End Sub
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
942
Office Version
  1. 365
Platform
  1. Windows
Or

VBA Code:
Sub jec()
 With Range("A1", Range("A" & Rows.Count).End(xlUp))
   .AutoFilter 1, Filter(Filter(Filter(Filter(Filter(Filter(Application.Transpose(.Value), "@Yahoo", 0, 1), "@Gmail", 0, 1), "@Hotmail", 0, 1), "@Live", 0, 1), "@ymail", 0, 1), "@msn", 0, 1), 7
 End With
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,602
Messages
5,832,665
Members
430,153
Latest member
Javid_P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top