Need to extract domain names containing only specific words (MAJOR BULK)

Maxwell314 · #1 12-02-2014, 02:32 AM

I have a list of around 150,000 domain names and I need to extract all the ones containing specific words. I have a list of 10,000 words that need to be searched for in these domain names.

Here is a short example:

List of domains

soccertraining.com
basketballguide.com
carrepair.com
localjobs.com

footballfan.com
vacationplanner.com
tennisteams.com

Say I want to extract all the domains containing "soccer", "basketball", "football", and/or "tennis".

This is the result I'm looking for:

soccertraining.com
basketballguide.com
footballfan.com
tennisteams.com

---

Can this be done with excel? I imagine there is a formula to do this but it may be difficult to run it on this large of a scale.

If anyone could help me with this I would really appreciate it!

macropod · #2 12-02-2014, 04:42 PM

Doing this via formulae would be very convoluted and slow. One alternative would be to use a macro to delete the unwanted rows, thus:

Code:

Sub DeleteURLs()
Application.ScreenUpdating = False
Dim LRow As Long, i As Long, j As Long, StrAddr(), bDel As Boolean
StrAddr = Array("soccer", "basketball", "football", "tennis")
With ActiveSheet
  LRow = .UsedRange.Rows.Count
  For i = LRow To 1 Step -1
    bDel = True
    With .Range("A" & i)
      For j = 0 To UBound(StrAddr)
        If InStr(.Value, StrAddr(j)) > 0 Then
          bDel = False
          Exit For
        End If
      Next
      If bDel = True Then
        .EntireRow.Delete
      End If
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

Maxwell314 · #3 12-02-2014, 06:00 PM

First, thanks for the reply.

Though I am not very familiar with excel and do not know how to use macros. Could you explain exactly how I apply to this code?

Is the somewhere specific in excel where I need copy and paste it?

macropod · #4 12-02-2014, 06:31 PM

1. Open the workbook
2. Press Alt-F11 to open the VB Editor
3. On the left you should see your workbook's name. Double-click on the 'ThisWorkbook' entry below that.
4. Paste the code into the main window
5. Press Alt-F11 to exit the VB Editor
6. Press Alt-F8 and choose 'DeleteURLs', then Run.
When the macro finishes, you should only have the entries containing "soccer", "basketball", "football", and/or "tennis" remaining. With 150,000 rows to process, it may take a minute or two.

Note that the macro assumes all the data are in column A.

Maxwell314 · #5 12-08-2014, 05:10 PM

Thanks a lot of the help macropod! It works well.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how can i restrict my account on outlook to send email only to a specific domain?	kclark	Outlook	3	10-19-2014 06:21 PM
Need VBA For Macro On How To Remove Specific Words	netchie	Word VBA	6	08-28-2012 03:37 PM
VBA code to extract specific bookmarks from multiple word files	Rattykins	Word VBA	4	06-27-2012 10:02 PM
Can't send to specific domain	gilligan1961	Outlook	19	02-18-2011 01:53 PM
How would I bulk select and copy highlights and then bulk paste them automatically?	copywriterpro	Word	0	04-19-2010 05:21 AM