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.
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