Text Functions‎ > ‎

### Extract Domains

Sample File:    DomainExtraction-UDF.xls
PROBLEM:
"I need to extract domain names from a long list of hostnames."

EXAMPLE
1

SOLUTION: User Defined Function called DOMAIN()
The following UDF is installed in a normal module.

#### CODE

Option Explicit

Function DOMAIN(cell As Range, RNG2 As Range)
'Author:    Jerry Beaucaire
'Date:      9/3/2010
'Summary:   extract the domain name from a hostname string

If cell.Cells.Count <> 1 Then
DOMAIN = "1 cell only for range1"
Exit Function
End If

Dim MyArr   As Variant
Dim v       As Long
Dim vFind   As Long
Dim buf     As String
On Error Resume Next

MyArr = Split(cell, ".")

For v = UBound(MyArr) To LBound(MyArr) Step -1
buf = MyArr(v) & "." & buf
vFind = Application.WorksheetFunction.Match(MyArr(v - 1), RNG2, 0)
If vFind > 0 Then
DOMAIN = MyArr(v - 2) & "." & MyArr(v - 1) & "." & Left(buf, Len(buf) - 1)
Exit Function
End If
vFind = Application.WorksheetFunction.Match(MyArr(v), RNG2, 0)
If vFind > 0 Then
DOMAIN = MyArr(v - 1) & "." & Left(buf, Len(buf) - 1)
Exit Function
End If
Next v

End Function

In the table above, it would be used in cell B2 as:
=DOMAIN(\$A2, \$E\$2:\$E\$20)

The first parameter is the cell to extract from.
The second parameter is a list of domain types used to spot the end of the domain name.

The final sheet would look something like:

EXAMPLE 2