Excel Macro to Extract URL from HYPERLINK Formula

August 26, 2014

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