Microsoft Access Extract Domain Name from Hyperlink or text field

Posted on 2009-04-29
Last Modified: 2013-11-27

I'm working on an access database that stores lists of URL's

My challenge (and I'm really hoping you can help) is to append 2 additional columns to the table that contains the URL's and in that column extract the domain and domain suffix.
For example, the table may contain the following column

What I'd like as the output for each of these rows is
Domain                             Suffix                   com                   com                        net                   com                     net                               info                    com

M Gandhi
Question by:fixyfoxy
    LVL 92

    Accepted Solution

    Hello fixyfoxy,

    Add the UDFs below to a regular VBA module, and run a query like this:

    SELECT URL, RegExpReplace(URL, "http://|[:/].*", "", True, False) AS Domain,
          Mid(RegExpFind(RegExpReplace(URL, "http://|[:/].*", "", True, False), "\.[a-z]{2,}$", 1, False), 2) AS Suffix
    FROM SomeTable


    Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
        Optional MatchCase As Boolean = True) 
        ' For more info see: 
        ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
        ' pattern (PatternStr).  Use Pos to indicate which match you want:
        ' Pos omitted               : function returns a zero-based array of all matches
        ' Pos = 0                   : the last match
        ' Pos = 1                   : the first match
        ' Pos = 2                   : the second match
        ' Pos = <positive integer>  : the Nth match
        ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
        ' returns an empty string.  If no match is found, the function returns an empty string
        ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
        ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
        ' If you use this function in Excel, you can use range references for any of the arguments.
        ' If you use this in Excel and return the full array, make sure to set up the formula as an
        ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
        Static RegX As Object
        Dim TheMatches As Object
        Dim Answer() As String
        Dim Counter As Long
        ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
        If Not IsMissing(Pos) Then
            If Not IsNumeric(Pos) Then
                RegExpFind = ""
                Exit Function
                Pos = CLng(Pos)
            End If
        End If
        ' Create instance of RegExp object
        If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
        With RegX
            .Pattern = PatternStr
            .Global = True
            .IgnoreCase = Not MatchCase
        End With
        ' Test to see if there are any matches
        If RegX.test(LookIn) Then
            ' Run RegExp to get the matches, which are returned as a zero-based collection
            Set TheMatches = RegX.Execute(LookIn)
            ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
            ' function's return value
            If IsMissing(Pos) Then
                ReDim Answer(0 To TheMatches.Count - 1) As String
                For Counter = 0 To UBound(Answer)
                    Answer(Counter) = TheMatches(Counter)
                RegExpFind = Answer
            ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
                Select Case Pos
                    Case 0                          ' Last match
                        RegExpFind = TheMatches(TheMatches.Count - 1)
                    Case 1 To TheMatches.Count      ' Nth match
                        RegExpFind = TheMatches(Pos - 1)
                    Case Else                       ' Invalid item number
                        RegExpFind = ""
                End Select
            End If
        ' If there are no matches, return empty string
            RegExpFind = ""
        End If
        ' Release object variables
        Set TheMatches = Nothing
    End Function
    Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
        Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True) 
        ' This function uses Regular Expressions to parse a string, and replace parts of the string
        ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
        ' whether all instances of the matched string are replaced (True) or just the first instance (False)
        ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
        ' set it to True
        ' If you use this function from Excel, you may substitute range references for all the arguments
        Static RegX As Object
        If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
        With RegX
            .Pattern = PatternStr
            .Global = ReplaceAll
            .IgnoreCase = Not MatchCase
        End With
        RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
        Set RegX = Nothing
    End Function

    Open in new window

    LVL 2

    Author Closing Comment


    Super solution! Worked exactly as I needed it to....


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    using Access 8 41
    SQL for monthly balance change 15 52
    Access - LDB lock file 4 24
    phpMyAdmin simple sql statement 3 21
    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now