How To Extract URLs From Hyperlinks In Excel

Are you struggling with extracting URLs from hyperlinks in Excel? You’re not alone. It’s a common struggle, especially when dealing with large sets of data. 

In this article, I’ll show you not just one, but TWO methods to extract URLs from hyperlinks in Excel.

Whether you prefer using custom formulas or VBA macros, I’ve got you covered. Say goodbye to the headache of manually extracting URLs and hello to a more efficient Excel-ing!

By the end of this article, you’ll be a pro at extracting URLs from hyperlinks in Excel, no matter how massive your data may be. So, let’s get started and learn how to extract URLs from hyperlinks in Excel!

Understanding Hyperlinks In Excel

Hyperlinks in Excel are a great way to link to other files, websites, or even specific locations within a file. They can be used to create a table of contents, navigate large workbooks, or provide easy access to external resources.

Hyperlinks in Excel are created by adding an address to a cell or a shape in the worksheet. When you click on the hyperlink, Excel will take you to the destination of the link.

Types Of Hyperlinks In Excel

Excel supports several types of hyperlinks, including:

  • URLs – These are hyperlinks that point to a website or web page. You can create a URL hyperlink by typing the URL directly into a cell or by using the Insert Hyperlink dialog box.
  • Email Addresses – These are hyperlinks that allow you to send an email to a specific email address. You can create an email hyperlink by typing the email address directly into a cell or by using the Insert Hyperlink dialog box.
  • File Links – These are hyperlinks that point to another file on your computer or network. You can create a file hyperlink by typing the file path directly into a cell or by using the Insert Hyperlink dialog box.
  • Place In This Document – These are hyperlinks that point to a specific location within the same workbook. You can create a place in this document hyperlink by selecting the cell or range you want to link to and then using the Insert Hyperlink dialog box.
Hyperlink example

Extract URLs From Hyperlinks

In this section, we’ll cover three methods: using the right-click method, using the HYPERLINK function, and using VBA code.

Using The Right-Click Method (Manual)

The right-click method is the simplest way to extract URLs from hyperlinks in Excel. This is a bit manual so I would only reserve this for a quick one-off task.

Here’s how to do it:

  1. Right-click on the cell containing the hyperlink
  2. Select “Edit Hyperlink” from the dropdown menu
  3. The URL will appear in the “Address” field. Copy the URL and paste it wherever you need it

Keyboard shortcut tip – Select the cell and press the keyboard combination Ctrl + K. CMD + K for Excel Mac users. This will open the “Edit Hyperlink” dialog box in Excel.

Using VBA Code

If you’re comfortable with VBA code, you can use it to extract URLs from hyperlinks in Excel. Here’s the code:

Sub GenerateURL()

Dim cell As Range

   For Each cell In Selection

      If cell.Hyperlinks.Count > 0 Then
         cell.Offset(0, 1).Value = cell.Hyperlinks(1).Address
      End If

   Next cell

End Sub

Select the cells where you’d like to extract the URLs and run the macro.

If you would like to set up a UDF to extract hyperlinks, here’s the code:

Function GenerateURL(cell As Range) As String

   On Error Resume Next
   GenerateURL = cell.Hyperlinks(1).Address

End Function

Now, in your Excel worksheet, you can use the function GenerateURL() to extract the URL from any cell. For example, if you want to extract the URL from cell A1, enter =GenerateURL(A1) in the cell where you want the URL to appear.

Converting URLs To Clickable Hyperlinks

Now that we’ve covered how to extract links, I may as well cover the reverse! In this section, I’ll show you two ways to convert URLs to clickable hyperlinks in Excel.

Using The HYPERLINK Formula

The easiest way to convert URLs to clickable hyperlinks is by using the HYPERLINK formula.

  1. Select the cell where you want the URL to appear. In the example below, I’ve used cell C1
  2. Type the following formula: =HYPERLINK(“cell reference”,”name”)
  3. Replace “cell reference” with the cell containing the URL, i.e. cell A1. The link name goes into the second parameter, i.e. cell B1.
  4. Press ENTER and the hyperlink will appear in the selected cell
  5. Drag the formula down as needed
Converting URL to link

Using VBA Code

Alternatively, you can use VBA to generate hyperlinks. Paste the following code into a module:

Sub ConvertToHyperlinks()

    For Each cell In Selection

        If cell <> "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=cell.Value
        End If

    Next

End Sub

Select the cell(s) containing the URLs you want to convert. Then run the macro. Voila!

Frequently Asked Questions

How can I change the appearance of a hyperlink in Excel?

The default hyperlink in Excel is a blue font and underlined. To change the appearance of a hyperlink in Excel, you can modify the cell’s style and format.

  • Right-click on the cell containing the hyperlink and select “Format Cells“. The keyboard shortcut is Ctrl+1
  • In the “Format Cells” dialog box, click on the “Font” tab
  • Here, you can change the font, font style, size, color, and other text effects
  • Once done with the changes, click on “OK”

Remember, these changes will only affect the visual appearance of the hyperlink, not the actual link.

What formula can I use to copy a hyperlink to another cell in Excel?

The easiest way to copy a hyperlink from one cell to another in Excel is by using the HYPERLINK formula. Here’s the formula you would use:

=HYPERLINK(link_location, [friendly_name])

The link_location is the text that represents the hyperlink’s URL. The [friendly_name] is optional and is the text that you want to display as the hyperlink.

You may also like to read:

Leave a Comment