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
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 Sub3. 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.
| Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
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?
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
