How to extract Web Address or Web URL from string VBA code example

How to extract Web Address or Web URL from string VBA code example

Web URL

Refers to web address pointing to a particular host. In some cases you might want to extract web address from a running string. In this post I am writing a generic function which can be called in any any Microsoft Office (Excel, PowerPoint, Microsoft Word, Outlook) to retrieve Web address or web URL.

Code example

Public Function GetWebAddress(strSource As String) As String
    GetWebAddress = ""
    On Error GoTo errh
    
    'Validate empty string
    If IsEmpty(strSource) = False And Len(strSource) > 0 Then
        'check if string contains @
        If InStr(1, LCase(strSource), "http:") > 0 Or InStr(1, LCase(strSource), "https:") > 0 Or InStr(1, LCase(strSource), "www.") > 0 Then
            Dim objEmail
            objEmail = Split(strSource, " ")
            Dim oElement
            For Each oElement In objEmail
                If InStr(1, oElement, "http:") > 0 Then
                    GetWebAddress = oElement
                ElseIf InStr(1, oElement, "https:") > 0 Then
                    GetWebAddress = oElement
                ElseIf InStr(1, oElement, "www") > 0 Then
                    GetWebAddress = oElement
                End If
            Next oElement
            Set oElement = Nothing
        Else
            GetWebAddress = "does not contain email"
            Exit Function
        End If
    Else
        GetWebAddress = "Please enter a valid string"
        Exit Function
    End If
errh:
    If Err.Number <> 0 Then
        GetWebAddress = Err.Description
        Exit Function
    End If
End Function

Explanation

Above function takes a string as input and finds a valid Web Url or Web Address, if found it returns web url back.

Implementation

Output

Next >> Extract Email Address from a string using VBA code

Leave a Reply

Your email address will not be published.