To extract the actual URl from the hyperlinks in Excel (the url address), you will need to create a function in Visual Basic. Don’t worry, it’ actually less complicated then you think.
- Press down ALT and F11 to open up the Microsoft Visual Basic for Applications.
- Click Insert then go to Module and insert the code below inside the module window.
1 2 3 4 | Function GetURL(pWorkRng As Range) As String 'Updateby20140520 GetURL = pWorkRng.Hyperlinks(1).Address End Function |
- Save the code before you close the window.
- Go back to your sheet and select a blank cell (where you want to display the URL) and type =GetURL(A1) formula.
- Press Enter and you’ll be able to see the hyperlink address is extracted.
That’s it!
