Text Functions‎ > ‎

Extract Domains

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

    
                                                                        EXAMPLE
1
  A B
1 INPUT DESIRED OUTPUT
2 101-162-201-123.static.youtele.com youtele.com
3 104.pool85-54-34.dynamic.orange.es not found
4 104.Red-83-56-127.dynamicIP.rima-tde.net rima-tde.net
5 105.80-202-99.nextgentel.com nextgentel.com
6 109-229-187-203.static.youtele.com youtele.com
7 110-234-49-200.del.tulipconnect.com tulipconnect.com
8 112.200.6.200.pldt.net pldt.net
9 112-105-214-129.adsl.dynamic.seed.net.tw seed.net.tw
10 115.108.15.202.static-bangalore.vsnl.net.in vsnl.net.in
11 115-64-184-216.tpgi.com.au tpgi.com.au
12 116.342.dsl.mel.iprimus.net.au iprimus.net.au
13 125-238-244-136.jetstream.xtra.co.nz xtra.co.nz
14 131-119-201-123.static.youtele.com youtele.com
15 134.115.in-addr.arpa in-addr.arpa
16 139.207.41.124.dynamic.wlink.com.np wlink.com.np
17 14.165.83.202.reverse.ntc.net.pk ntc.net.pk
18 142-194-201-123.static.youtele.com youtele.com
19 143.205.41.124.dynamic.wlink.com.np wlink.com.np
20 146.132.73-86.rev.gaoland.net gaoland.net
21 149.Red-81-38-50.dynamicIP.rima-tde.net rima-tde.net
22 1503032262.dhcp.dbnet.dk dbnet.dk
23 157.145.50.60.brk01-home.tm.net.my tm.net.my
24 158-184-91-219.static.youtele.com youtele.com
25 16.144.92.180-static-fiberlink.net.pk 180-static-fiberlink.net.pk
26 160.166.72-86.rev.gaoland.net gaoland.net
27 163.Red-81-38-248.dynamicIP.rima-tde.net rima-tde.net
28 164.84-49-85.nextgentel.com nextgentel.com
29 171-198-201-123.static.youtele.com youtele.com
30 173-14-80-194-naples.hfc.comcastbusiness.net comcastbusiness.net

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

DOMAIN = "not found"
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
 ABCDE
1INPUTDESIRED OUTPUT  Domain Types
2101-162-201-123.static.youtele.comyoutele.com  arpa
3104.pool85-54-34.dynamic.orange.esnot found  au
4104.Red-83-56-127.dynamicIP.rima-tde.netrima-tde.net  be
5105.80-202-99.nextgentel.comnextgentel.com  ch
6109-229-187-203.static.youtele.comyoutele.com  co
7110-234-49-200.del.tulipconnect.comtulipconnect.com  com
8112.200.6.200.pldt.netpldt.net  cy
9112-105-214-129.adsl.dynamic.seed.net.twseed.net.tw  dk
10115.108.15.202.static-bangalore.vsnl.net.invsnl.net.in  gov
11115-64-184-216.tpgi.com.autpgi.com.au  gr
12116.342.dsl.mel.iprimus.net.auiprimus.net.au  it
13125-238-244-136.jetstream.xtra.co.nzxtra.co.nz  mp
14131-119-201-123.static.youtele.comyoutele.com  my
15134.115.in-addr.arpain-addr.arpa  net
16139.207.41.124.dynamic.wlink.com.npwlink.com.np  nl
1714.165.83.202.reverse.ntc.net.pkntc.net.pk  pk
18142-194-201-123.static.youtele.comyoutele.com  ru
19143.205.41.124.dynamic.wlink.com.npwlink.com.np  sk
20146.132.73-86.rev.gaoland.netgaoland.net   
21149.Red-81-38-50.dynamicIP.rima-tde.netrima-tde.net   
221503032262.dhcp.dbnet.dkdbnet.dk   
23157.145.50.60.brk01-home.tm.net.mytm.net.my   
24158-184-91-219.static.youtele.comyoutele.com   
2516.144.92.180-static-fiberlink.net.pk180-static-fiberlink.net.pk   
26160.166.72-86.rev.gaoland.netgaoland.net   
27163.Red-81-38-248.dynamicIP.rima-tde.netrima-tde.net   
28164.84-49-85.nextgentel.comnextgentel.com   
29171-198-201-123.static.youtele.comyoutele.com   
30173-14-80-194-naples.hfc.comcastbusiness.netcomcastbusiness.net   

Spreadsheet Formulas
CellFormula
B2=DOMAIN($A2,$E$2:$E$20)


Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments