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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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