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
- Now use the function GetURL(A1) to extract the URL from the hyperlinked cell.
Good Luck ;)