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

[Maxwell314]

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]

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

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]

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]

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]

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