Microsoft Excel has a built in function called HYPERLINK() which
allows you to convert a link location (URL) and friendly name (anchor
text) into a hyperlinked cell that you can click on to open the link in a
web browser window. However, Excel does not have a default function
that allows you to do the reverse of that—to extract the URL from a
hyperlinked cell. In order to do this, we need to get our hands dirty
with some VBA (Visual Basic for Applications) code. Don’t worry, it
isn’t too complicated.
- Hold down Alt + F11 Key to Open the Microsoft Visual Basic for Applications window
- Go to Insert -> Module
- Paste the following into the Module Window:
Function GetURL(pWorkRng As Range) As String
GetURL = pWorkRng.Hyperlinks(1).Address
End Function - Now use the function GetURL(A1) to extract the URL from the hyperlinked cell.
Good Luck ;)
