Note: The other languages of the website are Google-translated. Back to English

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.


arrow blue right bubble 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.


arrow blue right bubble 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:


arrow blue right bubble 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.


arrow blue right bubble 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 doc-button1 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 !


arrow blue right bubble Demo: Extract actual addresses from hyperlinks with Kutools for Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related article:

How to convert url text to clickable hyperlink in Excel?


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (44)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thankyou so much. it really helped me
This comment was minimized by the moderator on the site
thanks so much!!! :D
This comment was minimized by the moderator on the site
This just saved me! Thanks
This comment was minimized by the moderator on the site
thnx u made my work so easy
This comment was minimized by the moderator on the site
Thank You SO SO much!
This comment was minimized by the moderator on the site
Thank you so much this saved my ass and make me look good. 8)
This comment was minimized by the moderator on the site
You are the best, so easy to follow!
This comment was minimized by the moderator on the site
Thanks buddy.It really work in seconds.
This comment was minimized by the moderator on the site
Thankyou so much. it really helped me
This comment was minimized by the moderator on the site
Thanks a lot! Very helpful yet simple and clear. Just as Albert meant :roll:
This comment was minimized by the moderator on the site
Super! So easy. Thanks!
This comment was minimized by the moderator on the site
thanks a ton. like everyone else here. this is very helpful and easy.
This comment was minimized by the moderator on the site
Thank you so much :lol: I used the VBA method and it worked like a charm in Excel 365 on a Windows 8.1 OS
This comment was minimized by the moderator on the site
This is the first ever macro based excel tip that worked for me. Thank you very much for making the lives of excel dummies better.
This comment was minimized by the moderator on the site
thanks for saving me 15mins
This comment was minimized by the moderator on the site
Superb! reduced my work to less than a minute.
This comment was minimized by the moderator on the site
This saved me a ton of time! I extracted 500 profile IDs from 500+ names linked to their profiles. This code worked extremely well and was easy to follow. Thanks so much!
This comment was minimized by the moderator on the site
For some reason this doesn't work for me. I created the defined function just as you described, but it doesn't do anything :\ I also want to merge it with the Hyperlink function, like so: =HYPERLINK(=getURL(A1), [A1]) So that the final result will copy A1's URL and display it as A1's text string (i.e. just copy A1 to another cell, including both its text and hyperlink. A simple thing which Excel doesn't seem to be able to do for some reason...).
This comment was minimized by the moderator on the site
Thanks so much! Saved me big time of having to do 800+ of these cells!
This comment was minimized by the moderator on the site
Very nice tool! I converted 16K+ links. Tool worked fast and flawless.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations