How to extract real addresses from Hyperlinks in Excel

When working with Excel files, we often come across files that contain many Hyperlinks. If you don’t know how to extract them or if your extraction is slow, Hyperlinks can be a big problem for you. This article will guide you on how to quickly extract real addresses from Hyperlinks in an Excel file using different methods.

Method 1: Extract real addresses of links using the Edit Hyperlink feature

The Edit Hyperlink feature can help you extract hidden URLs for each hyperlink, and put those real addresses into a different cell. You can do the following:
1. Select the cell with the hyperlink, right-click, and select Edit Hyperlink.

2. From the Edit Hyperlink dialog box, select and copy (Ctrl+C) the path from the Address box.

3. Close the dialog box and paste the link address into any cell you want.
Note: With this method, you can only extract one link address at a time. If you have multiple hyperlinks, you have to repeat the steps for each extraction.

Method 2: Extract real addresses from Hyperlinks with VBA code

For many hyperlinks, the above method will take a lot of time. Using VBA automation tools can help you extract multiple link addresses quickly at the same time.
1. Press ALT+F11 to open the Microsoft Visual Basic for Applications dialog box.
2. Press Insert> Module, and paste the following code:

3. Press F5 to run the code, a dialog box appears asking you to select the hyperlinks you want to extract link addresses from, as shown below:

4. Click OK, and the hyperlinks are converted to real addresses.

Method 3: Extract real addresses from hyperlinks with a user-defined function

The following custom function can extract real addresses from Hyperlinks.
1. Press ALT+F11 to open the Microsoft Visual Basic for Applications dialog box.
2. Press Insert> Module, and paste the following code: Function GetURL(pWorkRng As Range) As String ‘Updateby20140520
GetURL = pWorkRng.Hyperlinks(1).Address
End Function
3. Save the code and close the window, select an empty cell and enter the following formula: =GetURL(A2) (A2 is the cell containing the hyperlink), press Enter. You can see that the link address has been extracted.