How to extract actual addresses from hyperlinks in Excel?

Supposing you have a range of cells which contain hyperlinks, and now you need to see the real destination of the hyperlinks and extract them from the hyperlinks as following screenshot shown. Are there any easy ways to solve this problem quickly? This article will guide you to extract actual addresses from multiple hyperlinks.

Extract actual addresses from hyperlinks with Edit Hyperlink feature

Extract actual addresses from hyperlinks with VBA code

Extract actual addresses from hyperlinks with User Definde Function

Extract actual addresses from hyperlinks with Kutools for Excel quickly and easily

Extract actual addresses from hyperlinks with Edit Hyperlink feature

Here, the Edit Hyperlink function can help you extract the underlying URL for each of these hyperlinks and place that actual address into a different cell. You can do as this:

1. Select the cell with hyperlink and right-click, from the context menu, choose Edit Hyperlink, see screenshot:

2. And an Edit Hyperlink dialog box will be appeared, select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.

3. Then close the dialog box and paste the actual address into any cell you want.

Note:With this method, you can extract only one hyperlink address each time, but if you have multiple hyperlinks need to be extracted, you need to apply this function repeatedly.

Extract actual addresses from hyperlinks with VBA code

For a lot of hyperlinks, the above method will be tedious and time consuming, so the following VBA code can help you quickly extract multiple addresses from the hyperlinks at once.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Sub Extracthyperlinks()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Rng.Hyperlinks.Count > 0 Then
        Rng.Value = Rng.Hyperlinks.Item(1).Address
    End If
Next
End Sub

3. Then press F5 key to run the code, and a dialog pops out for you to select the hyperlinks that you want to extract the real addresses, see screenshot:

4. Then click OK, and the selected cell contents have been converted to the real hyperlink addresses in the original range. See screenshots:

Extract actual addresses from hyperlinks with User Definde Function

The following User Defined Function also can extract the actual URl from the hyperlinks.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Function GetURL(pWorkRng As Range) As String
'Updateby Extendoffice
    GetURL = pWorkRng.Hyperlinks(1).Address
End Function

3. Save the code and close the window, select a blank cell to type this formula =GetURL(A2) (A2 is the cell that the hyperlink in), and press Enter button. You can see the real hyperlink address is extracted.

Extract actual addresses from hyperlinks with Kutools for Excel quickly and easily

Here is a handy tool, called Kutools for Excel, with its Convert Hyperlinks feature, you can quickly deal with the following operations:

• Extract actual addresses from hyperlinks;
• Convert url text to clickable hyperlinks.

If you have installed Kutools for Excel, please do as follows:

1. Select the range which contains the hyperlinks need extracting.

2. Click Kutools > Link > Convert Hyperlinks, see screenshot:

3. In the Convert Hyperlinks dialog box, select Hyperlinks addresses replace cell contents option, and click button from Result range to specify a cell to put the result.

4. And then click OK, the actual addresses have been extracted from the hyperlinks. See screenshot:

Note: If you want to put the actual addresses to the original range, please check Convert source range.

Click Convert Hyperlinks to know more about this feature.

Download and free trial Kutools for Excel Now !

Demo: Extract actual addresses from hyperlinks with Kutools for Excel

Related article:

How to convert url text to clickable hyperlink in Excel?