How to Extract URLs from Hyperlinks in Excel

Having something a click away, hyperlinks are precisely what you're talking about. But instead of jumping to a webpage, what are we to do when we need to dial things back a little?

Say we have some clickable links but require the actual URL behind them instead of the face of the URL in the form of a display name. If we are to share the links, we may often require them in URL form. For a practical example, have a look below:

The telltale underlined blue font is evidence of hyperlinks and looking at it, you can tell that the actual URL is lying under. Hovering over the hyperlink, we can see the base link and that is what we are after:

If you're wondering why despite the grand ease of hyperlinks would we need URLs, you present a really good question. The web addresses or URLs can be needed when, instead of navigating, the link itself requires work. You may want the URL for sharing, evaluation, correct referencing, comparing links of a set of hyperlinks, analyzing links, creating databases, etc.

Did we cover why you need the URLs? We can show you three simple ways to extract URLs from hyperlinks with the Edit Hyperlink option, a user-defined function, and VBA. You won't need any prior knowledge; we've got this for you. Just enter your URLs on a spreadsheet and choose a method from below.

Let's get linking!

Method #1 – Using Edit Hyperlink Option

Use Excel's Edit Hyperlink option to extract the URL from a hyperlink. The option is accessible just a right-click away. This method works on an individual hyperlink and is therefore best if you want a single URL or maybe two. It's a super speedy way of cracking a hyperlink but if you're working with more, we suggest you take one of the other two methods mentioned in this tutorial.

For now, the steps given below are on how to use the Edit Hyperlink option to get the URL from a hyperlink in Excel:

• Select the cell with the hyperlink (by clicking on a free space in the cell or navigating from the keyboard). Then press the Ctrl + K Alternatively, directly right-click the hyperlink cell and select the Edit Hyperlink option from the context menu.
• 
• You will now see a dialog box titled Edit Hyperlink.
• Copy the URL from the Address
• 
• Close the dialog box. You can use the Enter or Esc key to do this.
• Back to the worksheet, paste the copied URL to the preferred cell. You may even overwrite the original hyperlink if you choose to. We have pasted the URL next to the hyperlink.

Repeat for the remaining links but this is not how we're going to go about that task. You don't have to work on every hyperlink individually since other methods will do it for you. Read ahead!

Method #2 – Using User-Defined Function

A user-defined function will take a hyperlink in Excel and deliver its URL separately. The function is entered using VBA which is a grand tool for automating tasks in Office applications. Once the function is fed, it can be used like a regular Excel function.

The advantage of this method is tailored usage; you can use it for as little or as many hyperlinks and readily get multiple URLs. So let's find out what prep we need done. The complete instructions for extracting URLs from hyperlinks with a user-defined function are as follows:

• To open the Visual Basic editor, click on the Visual Basic button in the Developer tab's Code If the Developer tab is absent from your toolbar, press the Alt + F11 keys.
• 
• In the editor, click on the Insert tab and select Module from the drop menu.
• 
• In the Module window that launches, copy and paste the code given here:

• Function ExtractURL(rng As Range) As String
  On Error Resume Next
  ExtractURL = rng.Hyperlinks(1).Address
  End Function

  The code will set a custom function for extracting the URL from the provided hyperlink. Make note of the name of the function you are using in the first line. This is the name by which the function will appear in the worksheet. E.g. the function is named "ExtractURL" in our case example. Don't worry about the letter case, the function name will be case-insensitive on the sheet.The second line of the code ensures a neat output as the function will not result in an error if a cell doesn't contain a hyperlink. This fits well for our case example as we'll be using the function along a range with hyperlinks, blank cells, and text.

• Close the Visual Basic
• Enter the name of the function. As you type, the IntelliSense will display the complete function. You can select it from there. Next, enter the cell with the hyperlink (B4 in our case).

The complete formula for the first hyperlink in our example case hence is:

The custom function's result is shown below:

We have utilized the function to get all our URLs from the hyperlinks. You can see that it has produced no errors despite the absence of hyperlinks in cells B7 and B8. The function can be used wherever required on the sheet. We also have another option if you surefire want all the URLs displayed next to the hyperlinks. Method VBA coming right up!

Recommended Reading: How to Remove Hyperlinks in Excel

Method #3 – Using VBA

Use VBA in Excel to get the full URLs from hyperlinks. This time we're using a code to complete a task which is to enter the URL of every hyperlink in the worksheet, next to the hyperlink itself.

The code will automatically detect all the hyperlinks present in the sheet so make sure the column adjoining the hyperlinks is free. The following steps will guide you on using VBA to extract URLs from hyperlinks:

• Right-click the sheet tab of your worksheet and select the View Code option therein.
• 
• This will open The Visual Basic editor with a window for the code.
• Paste this code in the window:

• Sub ExtractURL()
  Dim HL As Hyperlink
  For Each HL In ActiveSheet.Hyperlinks
   HL.Range.Offset(0, 1).Value = HL.Address
  Next
  End Sub

  This code ensures to catch each hyperlink in the active sheet and returns the hyperlink's address 0 rows down and 1 column across from the hyperlink. That means in the cell right of the hyperlink cell – accomplished with the Offset property in the code.

• Hit the Run button in the editor (highlighted above) or the F5 key to run the code.
• Every hyperlink in the sheet will have its underlying address extracted in the adjacent cell:

This tutorial has provided you with all the core methods of attaining URLs from hyperlinks in Excel. Now you know what route to take when you need to deal with a single hyperlink or a host of them. There is so much more Excel expertise waiting for you, we hope you'll be back for it. Ready? Tricky? Go!