Help with segregating Excel Email List

M

Maverick27

Active Member
Joined
Sep 23, 2010
Messages
333
Office Version
  1. 2013
Platform
  1. Windows
Hi

I have a Excel Email List of 500K Contacts ( listed on Col A ).

The list is a mix of corporate, hotmail, gmail, yahoo..etc

Anyone care to share a Macro Code to display all emails on a separate Worksheet which does NOT include the following domains:


yahoo.co.uk,yahoo.com,yahoo.fr,googlemail.com,gmail.com,hotmail.com,windowslive.com,live.com,live.co.uk,ymail.com,kra.go.ke,africaonline.co.ke,postbank.co.ke,rocketmail.com,centralbank.go.ke,kcaa.or.ke,kengen.co.ke,kplc.co.ke,kpc.co.ke,kicc.co.ke,standardmedia.co.ke,kpa.co.ke,iconnect.co.ke,kcb.co.ke,swiftglobal.co.ke,outlook.co.ke,deloitte.co.ke,knsl.co.ke,yu.co.ke,kenyaairports.co.ke,nairobiwater.co.ke,yu.co.ke,

Below is a sample Mini Sheet

500k-V2.xlsx
A
1EMAIL
2abdicm@yahoo.com
3abdida@unhcr.org
4abdidabaso@yahoo.com
5abdidahiram@gmail.com
6abdidahirr@yahoo.com
7abdidayow20@hotmail.com
8abdideco@ymail.com
9abdidek.yakub@yu.co.ke
10abdidere@live.com
11abdidiad@yahoo.com
12abdidibah@yahoo.com
13abdidika@yahoo.co.uk
14abdidirir@yahoo.com
15abdido1@gmail.com
16abdidoqe@yahoo.com
17abdidorre1@gmail.com
18abdidubow@yahoo.com
19abdie.m.sebbaale@gmail.com
20abdieid@yahoo.com
21abdiel@gmail.com
22abdifa2000@yahoo.com
23abdifanta@yahoo.com
24abdifarahgure@yahoo.com
25abdifat1@yahoo.com
26abdifatah.omar@gmail.com
27abdifatah.omar75@gmail.com
28abdifatah.omar75@gmil.com
29abdifatah.osman@hotmail.com
30abdifatah.osman@yahoo.com
31abdifatah@cargen.com
32abdifatah@msn.com
33abdifatah_roble@yahoo.com
34abdifatah3@yahoo.com
35abdifatah39@hotmail.com
36abdifatahbillow@yahoo.com
37abdifatahdahir@yahoo.com
38abdifatahogle@hotmail.com
39abdifauzia@yahoo.com
40abdig_1999@yahoo.com
41abdigaler@yahoo.com
42abdigalma@yahoo.com
43abdigani.jama@gmail.com
44abdigani1@yahoo.com
45abdigarane@hotmail.com
46abdigasco@gmail.com
47abdigedi75@yahoo.com
48abdigger@yahoo.com
49abdigo@yahoo.com
50abdigur39@yahoo.com
51abdiguun@yahoo.com
52abdiguyo2004@yahoo.com
53abdih@stanbic.com
54abdih@yahoo.com
55abdihaafidh@gmail.com
56abdihafida@yahoo.com
57abdihaj@yahoo.com
58abdihakim.aynte@gmail.com
59abdihakim.ibrahim@gulfenergy.co.ke
60abdihakim.keems@gmail.com
61abdihakim03@yahoo.com
62abdihakim114@hotmail.com
63abdihakim20@hotmail.com
64abdihakim77@gmail.com
65abdihakimceynte@gmail.com
66abdihakimoho@yahoo.com
67abdihaliim@hotmail.com
68abdihalim115@gmail.com
69abdihalima08@yahoo.com
70abdihamid@yahoo.com
71abdihamid100@yahoo.com
72abdihamid82@gmail.com
73abdihamid82@yahoo.com
74abdihamid98@gmail.com
75abdihamid98@yahoo.co.uk
76abdihamiddaud@yahoo.com
77abdiharamhussein@yahoo.com
78abdihiye@yahoo.com
79abdihussein2000@yahoo.com
80abdihusseinabdi@yahoo.com
81abdihusseinhaji@yahoo.com
82abdii02@yahoo.com
83abdii15@yahoo.com
84abdiiaziiz@yahoo.com
85abdiibrahim28@yahoo.com
86abdiibrahim97@yahoo.com
87abdiilah@hotmail.com
88abdiism@gmail.com
89abdijama@saacid.org
90abdijama@yahoo.com
91abdijamal8@yahoo.com
92abdijelle@hotmail.com
93abdijeri4@yahoo.com
94abdijesto@hotmail.com
95abdi-jeylani@yahoo.com
96abdijjke@yahoo.com
97abdijuma2@gmail.com
98abdijuma2000@yahoo.com
99abdik@yahoo.com
100abdik_1974@yahoo.com
101abdik2004@yahoo.com
102abdik26@hotmail.com
103abdik4670@yahoo.com
104abdik888@hotmail.com
105abdikaadir@gmail.com
106abdikaadir08@hotmail.com
107abdikadar.mohamed@yahoo.com
108abdikadir.mohammed@president.go.ke
109abdikadir@gascoenergies.com
110abdikadir@yahoo.com
111abdikadir_2006@yahoo.com
112abdikadir_nurama@yahoo.com
113abdikadir080@hotmail.com
114abdikadir114@yahoo.com
115abdikadir25@yahoo.com
116abdikadir3b@yahoo.com
117abdikadiradan@yahoo.com
118abdikadirdahir@yahoo.com
119abdikadirh@gmail.com
120abdikadirhajir@yahoo.com
121abdikadiromar19@yahoo.com
122abdikadirore@yahoo.com
123abdikadirot@yahoo.com
124abdikadirrovic@hotmail.com
125abdikadirshe@yahoo.com
126abdikadirsheikh@yahoo.com
127abdikafi.abdullahi@wfp.org
128abdikafi.abikar@wellsfargo.com
129abdikafi.yusuf@fao.org
130abdikafi2007@yahoo.com
131abdikain@yahoo.com
132abdikani@oilcitylimited.com
133abdikani@somaliaid.net
134abdikani10@yahoo.com
135abdikaos@yahoo.com
136abdikarim.a@gmail.com
137abdikarim.prismtech@gmail.com
138abdikarim@hasspetroleum.com
139abdikarim@telesom.net
140abdikarim@unops.org
141abdikarim01@gmail.com
142abdikarim114@yahoo.com
143abdikarim1426@yahoo.com
144abdikarim1649@yahoo.com
145abdikarimderia@yahoo.com
146abdikarimy@gmail.com
147abdikaringas@yahoo.com
148abdikawi@hotmail.com
149abdike2@yahoo.com
150abdikerimtube@yahoo.com
151abdikey@gmail.com
152abdikib@yahoo.com
153abdikisos@yahoo.com
154abdikmoon@yahoo.co.uk
155abdikoskei0776@yahoo.com
156abdikulo@yahoo.com
157abdikurewa@gmail.com
158abdiladif@gmail.com
159abdilahi@dyerandblair.com
160abdilahikhamis@yahoo.com
161abdilahirabi@gmail.com
162abdilahiyus@yahoo.com
163abdilatif.haro@yahoo.com
164abdilatif.maalim@gmail.com
165abdilatif_mohamed@dai.com
166abdilatif345@yahoo.com
167abdilatifali@gmail.com
168abdilatifey@yahoo.com
169abdilawale@yahoo.com
170abdiletiwa@yahoo.com
171abdilhu@yahoo.co.uk
172abdillahi.ali@zain.com
173abdillahi@dyerandblair.com
174abdillahiali@hotmail.com
175abdillahimaoulida@yahoo.fr
176abdillahiug@hotmail.com
177abdille@gmail.com
178abdille2000@yahoo.com
179abdillebook@yahoo.com
180abdillefarma@yahoo.com
181abdillefatma@yahoo.com
182abdillesadya@gmail.com
183abdilx@yahoo.com
184abdim@kebs.org
185abdi-m@yahoo.com
186abdim30@yahoo.com
187abdimabass@gmail.com
188abdimahat.osman@kpc.co.ke
189abdimahdi02@hotmail.com
190abdimajid_yussuf@yahoo.com
191abdimalik@trojan.co.ke
192abdimanandido@yahoo.com
193abdimaslah@gmail.com
194abdimaslah@yahoo.com
195abdimnoma@gmail.com
196abdimohamed08@yahoo.com
197abdimohamedhapi2005@yahoo.com
198abdimohammed2002@gmail.com
199abdimohammed2012@gmail.com
200abdimtc@yahoo.com
201abdimte@yahoo.com
202abdimusa88@gmail.com
203abdimusamm@gmail.com
204abdimusamm@yahoo.com
205abdinaelmi@yahoo.com
206abdinasir.aftin@hotmail.com
207abdinasir.dream@gmail.com
208abdinasir@dialafrica.org
209abdinasir@steadman-group.com
210abdinasir2434@yahoo.com
211abdinasirabass@yahoo.com
212abdinasirahmed53@yahoo.com
213abdinasirmoha@yahoo.com
214abdinasirover@gmail.com
215abdinasirr@hotmail.com
216abdinassir@toplinetradersltd.com
217abdinassir74@yahoo.com
218abdinassirh@unops.org
219abdinassirhassan@gmail.com
220abdinassiromar2@hotmail.com
221abdinazir9@yahoo.com
222abdinge@hotmail.com
223abdingurusi@yahoo.com
224abdinoor.abdow@nic-bank.com
225abdinoor58@yahoo.com
226abdinoorali@gmail.com
227abdinoorf@yahoo.com
228abdinoorm@gmail.com
229abdinoormohamed@yahoo.co.uk
230abdinur@sypd.org
231abdinur@yahoo.com
232abdinur04@yahoo.com
233abdinur52@hotmail.com
234abdinur822@yahoo.com
235abdinurama@yahoo.com
236abdinurdude@hotmail.com
237abdinurmusa@gmail.com
238abdinurow2002@yahoo.com
239abdinuuh_5@hotmail.com
240abdioanei@yahoo.com
241abdioba@yahoo.com
242abdiomar82@yahoo.com
243abdiomari62@yahoo.com
244abdiosman9@yahoo.com
245abdiow14@gmail.com
246abdipolite@yahoo.co.uk
247abdiqadir_hussein@yahoo.com
248abdiqadir49@yahoo.com
249abdiqahar_adan@yahoo.com
250abdiqan@yahoo.com
251abdiqani007@hotmail.com
252abdiqani199@hotmail.com
253abdiqanijaamac56@hotmail.com
254abdirach@gmail.com
255abdirahiim2007@hotmail.com
256abdirahim@rocketmail.com
257abdirahim@students.uonbi.ac.ke
258abdirahim100@gmail.com
259abdirahim24@yahoo.com
260abdirahimcade@gmail.com
261abdirahimf@yahoo.com
262abdirahimsiko@yahoo.com
263abdirahmad@hotmail.com
264abdirahmad@yahoo.com
265abdirahman.adan@hashienergy.com
266abdirahman.adan@yahoo.com
267abdirahman.ali@theirc.org
268abdirahman.bashir.atom@gmail.com
269abdirahman.egal@deltahandling.com
270abdirahman@hotmail.com
271abdirahman_2@hotmail.com
272abdirahman_abdudo@yahoo.co.uk
273abdirahman_bashri@yahoo.com
274abdirahman_bestey@hotmail.com
275abdirahman_maxamed@yahoo.com
276abdirahman031@gmail.com
277abdirahman1427@yahoo.com
278abdirahman2001@yahoo.com
279abdirahman3939@hotmail.com
280abdirahman60@yahoo.com
281abdirahman65@yahoo.com
282abdirahmana@hotmail.com
283abdirahmanadan2000@yahoo.com
284abdirahmanaliow@yahoo.com
285abdirahmanbadai@yahoo.com
286abdirahmanbarkhad@gmail.com
287abdirahmanc@gmail.com
288abdirahmanelle101@hotmail.com
289abdirahmaney@hotmail.com
290abdirahman-f@yahoo.com
291abdirahmanhj@gmail.com
292abdirahman-ismail2001@yahoo.com
293abdirahmanpilot@yahoo.com
294abdirahmm@hotmail.com
295abdiramanbiko@yahoo.com
296abdiramata@yahoo.com
297abdirashid.abdi@equitybank.co.ke
298abdirashid.haji@concern.net
299abdirashid.khalif@gmail.com
300abdirashid3@hotmail.com
301abdirashid75@yahoo.com
302abdirashid8@hotmail.com
303abdirashid99@hotmail.com
304abdirashidabdullahi@yahoo.com
305abdirashidamina@yahoo.com
306abdirashidkulu@yahoo.com
307abdirashidmohamed15@yahoo.com
308abdirashiid18@yahoo.com
309abdirauf114@yahoo.com
310abdiraxman2004@hotmail.com
311abdirazak@hasspetroleum.com
312abdirazak_bat@yahoo.com
313abdirazaks@yahoo.com
314abdirehman.yusuf@gmail.com
315abdiri07@hotmail.com
316abdirihman15@yahoo.com
317abdirisak.hussein@gmail.com
318abdirisakov@yahoo.com
319abdirisaqnur@hotmail.com
320abdiriz@yahoo.com
321abdiriz87@yahoo.com
322abdirizack@ktdateas.com
323abdirizack1@hotmail.com
324abdirizacknoor@gmail.com
325abdirizacksheikh@yahoo.com
326abdirizak.issack@gmail.com
327abdirizak@hotmail.com
328abdirizak2006@gmail.com
329abdirizak26@yahoo.com
330abdirizak85@yahoo.com
331abdirizak94@hotmail.com
332abdirizakcov@yahoo.com
333abdirizakdhere@hotmail.com
334abdirizakgaile@yahoo.com
335abdirizakhm@yahoo.com
336abdirizakhussein@yahoo.com
337abdirizakkalicha07@yahoo.com
338abdirizakov@yahoo.com
339abdirizaksts@yahoo.com
340abdirizakw@gmail.com
341abdirono@yahoo.com
342abdirybiduz@yahoo.com
343abdis@irinnews.org
344abdisadat@yahoo.com
345abdisadinka@hotmail.com
346abdisahard@yahoo.com
347abdisaidi@gmail.com
348abdisalam696@yahoo.com
349abdisalam77@gmail.com
350abdisalamshariffidris@yahoo.com
351abdisalan.omar@hasspetroleum.com
352abdisalan772000@yahoo.com
353abdisalat_4@yahoo.com
354abdisalat22@yahoo.com
355abdisamad@umma.ac.ke
356abdisandere@hotmail.com
357abdisdo@yahoo.com
358abdish@rogers.com
359abdishakur.othowai@gmail.com
360abdishakurimohamud@yahoo.com
361abdishakurm@hotmail.com
362abdisheikhm@yahoo.com
363abdishekur@unfpa.org
364abdishida@yahoo.com
365abdishukri2002@yahoo.com
366abdisitar2005@hotmail.com
367abdisitaryussuf@yahoo.com
368abdismart@hotmail.com
369abdismart2011@hotmail.com
370abdisowfa@hotmail.com
371abdisrmy@yahoo.com
372abdissama@yahoo.com
373abdistar2005@yahoo.com
374abdisuez9@yahoo.com
375abdiwahab.haji@waha-international.org
376abdiwahab.haji08@gmail.com
377abdiwahab.ibrahim@oxfamnovib.or.ke
378abdiwahab@yahoo.com
379abdiwahab1@gmail.com
380abdiwahab17@gmail.com
381abdiwahab256@hotmail.com
382abdiwahab256@yahoo.com
383abdiwahab33@hotmail.com
384abdiwahaba@yahoo.com
385abdiwahabmm@yahoo.com
386abdiwahi@unhcr.org
387abdiwali88@yahoo.com
388abdiwarsan@yahoo.com
389abdiweli.adam@yahoo.com
390abdiweli53@hotmail.com
391abdiweli8@hotmail.com
392abdiwelio@yahoo.com
393abdiya3hassan@yahoo.com
394abdiyaa04@yahoo.com
395abdiyahassan@yahoo.com
396abdiyakub@yahoo.com
397abdiyalah@gmail.com
398abdiyara@yahoo.co.uk
399abdiyare382@hotmail.com
400abdiyey_09@yahoo.com
401abdiyow101@live.com
402abdiyow5@yahoo.com
403abdizaed@hotmail.com
404abdize@live.com
405abdize03@gmail.com
406abdizeila@gmail.com
407abdjamahassan@airfrance.fr
408abdlorot@yahoo.com
409abdlquadri@googlemail.com
410abdlusebago@yahoo.com
411abdmakueni@yahoo.com
412abdmazrui@yahoo.com
413abdmjd2@yahoo.com
414abdmulawa@yahoo.com
415abdnet_som@yahoo.com
416abdo.dubless.moha@gmail.com
417abdo.moussaarreh@yahoo.fr
418abdo_aden_okie@yahoo.fr
419abdo_dev@hotmail.com
420abdo_elmosharf@hotmail.com
421abdo11479@yahoo.com
422abdoabi@yahoo.com
423abdoadam76@yahoo.com
424abdoadhi@yahoo.com
425abdobahajj55@yahoo.com
426abdocal@yahoo.com
427abdofmoha@yahoo.com
428abdogz@yahoo.com
429abdokhidir@hotmail.com
430abdollahpeyman@me.com
431abdomar88@gmail.com
432abdon13@yahoo.com
433abdonbirungi@yahoo.com
434abdoollye@yahoo.com
435abdosh17@hotmail.com
436abdosh68@gmail.com
437abdosman@gmail.com
438abdou.tenkouano@worldveg.org
439abdou_id2000@yahoo.fr
440abdou_koze@yahoo.fr
441abdou_nassur@yahoo.com
442abdou_nassur@yahoo.fr
443abdou8230@yahoo.fr
444abdouahmed50@gmail.com
445abdouamina@gmail.com
446abdouazali@yahoo.fr
447abdoubidou@gmail.com
448abdoudangoma@yahoo.fr
449abdoui@msn.com
450abdoujammeh777@hotmail.com
451abdoujatta_3@hotmail.com
452abdoukaambi@hotmail.fr
453abdouklkarim_moussa@yahoo.fr
454abdoul_bonk@yahoo.fr
455abdoulafourou@yahoo.com
456abdoulaye.dioum@gmail.com
457abdoulayecham@hotmail.com
458abdoulayes@gmail.com
459abdoulaziz.moussa@hotmail.com
460abdoulbastoi@yahoo.fr
461abdoulhakim@gmail.com
462abdoulie.jawo@rescue.org
463abdouliejam@yahoo.com
464abdouliejohn@yahoo.fr
465abdoulkader.hassan@yahoo.fr
466abdoulkader.ismail@fao.org
467abdoulkaderibrahim@gmail.com
468abdoulkarim_mohamed@yahoo.fr
469abdoulkarimmoussa@gmail.com
470abdoulnasser_1971@yahoo.fr
471abdounassur@hotmail.com
472abdouousenseni@yahoo.fr
473abdourahim_younoussa@yahoo.fr
474abdouramanb@yahoo.com
475abdourazak_gadileh@hotmail.com
476abdoussalam-08@yahoo.com
477abdousultan@gmail.com
478abdouwahil02@yahoo.com
479abdouwardaty@yahoo.fr
480abdow_20@yahoo.com
481abdow1000@yahoo.com
482abdow6mohamed@yahoo.com
483abdowjnr@yahoo.com
484abdoya@yahoo.com
485abdrahman.abdalah@yahoo.com
486abdrahmanbiko@yahoo.com
487abdrahmane94@yahoo.fr
488abdrasheikh@yahoo.com
489abdsu_74@yahoo.co.uk
490abdsulem@yahoo.com
491abdtwa@yahoo.com
492abdu.mukasa@finance.go.ug
493abdu.sar@gmail.com
494abdu_faraji@hotmail.com
495abdu_koki@yahoo.com
496abdu010101@yahoo.com
497abdualih@yahoo.com
498abdub_80@yahoo.com
499abduba.isacko@equitybank.co.ke
500abdubagodana@gmail.com
500k + csv validated




Thanks in Advance
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Assuming data is in 'Sheet1' and results in 'Sheet2' (edit code for either sheet name) try this.

VBA Code: 
Sub List_Emails()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim Exclude As String
  
  Exclude = "yahoo.co.uk,yahoo.com,yahoo.fr,googlemail.com,gmail.com,hotmail.com,windowslive.com,live.com,live.co.uk,ymail.com,kra.go.ke" _
            & ",africaonline.co.ke,postbank.co.ke,rocketmail.com,centralbank.go.ke,kcaa.or.ke,kengen.co.ke,kplc.co.ke,kpc.co.ke,kicc.co.ke" _
            & ",standardmedia.co.ke,kpa.co.ke,iconnect.co.ke,kcb.co.ke,swiftglobal.co.ke,outlook.co.ke,deloitte.co.ke,knsl.co.ke,yu.co.ke" _
            & ",kenyaairports.co.ke,nairobiwater.co.ke,yu.co.ke"
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = "@(" & Replace(Exclude, ",", "|") & ")$"
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Not RX.Test(a(i, 1)) Then
      k = k + 1
      b(k, 1) = a(i, 1)
    End If
  Next i
  If k > 0 Then Sheets("Sheet2").Range("A1").Resize(k).Value = b
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

7
  • Question Question
Excel - if cell contains..
Replies
3
Views
700
Peter_SSs
Peter_SSs
R
  • Question Question
Find and Replace Many
Replies
5
Views
1K
hiker95
H
H
  • Question Question
How to Delete all email domains with this list
Replies
2
Views
1K
hibbardr89
H
D
  • Question Question
[Excel VBA]Parse & Send an email multiple worksheets to multiple recipients using Gmail
Replies
0
Views
3K
dylanrose
D

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

Share this page

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
Back
Top