Excel Macro to Extract URL from HYPERLINK Formula

After searching the internet for a macro to extract the url of a hyperlink I found numerous possibilities, but none worked. After further inspection of my hyperlink, what I needed to do was extract the URL from the HYPERLINK formula, not the Address of a hyperlink. There is a difference. Here I share the code I used to make this happen.

This code will allow you to select several cells with HYPERLINK formulas and then use my macro to replace the formula with just the URL.

Sub ReplaceHyperlinkWithUrl()
Dim s As Object
Dim oldString As String

For Each s In Selection
oldString = s.Formula

Dim startIndex As Long
startIndex = Len(oldString) – 12
oldString = Right(oldString, startIndex)

Dim endIndex As Long
endIndex = InStr(oldString, “”””)
oldString = Left(oldString, endIndex – 1)

s.Formula = oldString
Next s
End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: