Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 Kutools for Excel’s Convert Hyperlinks feature, you can quickly extract the real URL addresses from the hyperlinks, at the same time, you can also convert the URL text to clickable hyperlinks.

doc change hyperlink browser 6

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


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.


Remove all hyperlinks from selected range, sheets or whole workbook at once:

With Kutools for Excel's Delete Hyperlinks function, you can remove all the hyperlinks from a selected range, sheets or the whole workbook as you need.

doc remove hyperlinks 4


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()
'Updateby20140318
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
'Updateby20140520
    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 60 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 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related article:

How to convert url text to clickable hyperlink in Excel?


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Arvid Podsim · 8 months ago
    Trying to save time creating multiple Hyperlinks and hoped this program would help with the task. I have combined several columns containing text into a single cell and then used the Edit/Link to create a working URL. Is there a tool to modify the URL to display text characters of the URL.
  • To post as a guest, your comment is unpublished.
    sujan maharjan · 1 years ago
    Hi, How to do the same in google sheets
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      hello, sujan,
      To extract all real addresses from hyperlinks in Google sheets, the following script code can help you:

      function linkURL(reference) {
      var sheet = SpreadsheetApp.getActiveSheet();
      var formula = SpreadsheetApp.getActiveRange().getFormula();
      var args = formula.match(/=\w+\((.*)\)/i);
      try {
      var range = sheet.getRange(args[1]);
      }
      catch(e) {
      throw new Error(args[1] + ' is not a valid range');
      }
      var formulas = range.getFormulas();
      var output = [];
      for (var i = 0; i < formulas.length; i++) {
      var row = [];
      for (var j = 0; j < formulas[0].length; j++) {
      var url = formulas[i][j].match(/=hyperlink\("([^"]+)"/i);
      row.push(url ? url[1] : '');
      }
      output.push(row);
      }
      return output
      }

      After copying and pasting the above code, go back to your sheet, and apply this formula:=linkURL(A2).
      Please try it, hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    Victor Nieto · 1 years ago
    Thanks!!!!!
  • To post as a guest, your comment is unpublished.
    Neeks · 1 years ago
    Ah, amazing! Thank you!
  • To post as a guest, your comment is unpublished.
    sei · 1 years ago
    pretty..and nice...and fantabulouslyfntastikittilybumpershumperazardubiliousipaparazziastically yummy :P
    huh!