How to Extract URL from Hyperlink in Excel Formula?

Sharing is caring!

Have you ever needed to extract just the URL from a hyperlink in Microsoft Excel, but weren’t sure how to do it? Hyperlinks in Excel cells usually display text rather than the actual URL. But in many cases, you may want to get the URL itself from the hyperlink. Luckily, Excel provides a few straightforward methods to pull the URL out of a hyperlink using formulas. In this article, we’ll walk through exactly how to extract a URL from a hyperlink in Excel, step-by-step.

Understanding Hyperlinks in Excel

First, let’s cover some basics about how hyperlinks work in Microsoft Excel. A hyperlink in an Excel cell is a reference to a web page URL. When you click the cell, it will open that URL in your default web browser.

Hyperlinks consist of two main parts:

1. The display text – This is the text that is shown in the cell and that you click on to open the hyperlink
2. The URL (Uniform Resource Locator) – This is the actual web address that the hyperlink points to, even though it’s not displayed in the cell

So when you see a hyperlink in a cell, Excel is really storing both the display text and URL behind the scenes, even though it only shows the display text.

Method 1: Using the HYPERLINK Function

Excel has a built-in function called HYPERLINK that allows you to extract just the URL from a cell containing a hyperlink. Here’s how to use it:

1. Start with a cell that contains a hyperlink. For this example, let’s say the hyperlink is in cell A2.
2. In a different cell, type the formula: =HYPERLINK(A2)
3. Press Enter, and you should see the URL appear in the cell with the formula.

The HYPERLINK function takes the cell reference as input and returns just the URL portion of the hyperlink. Super simple!

Method 2: Combining the RIGHT, LEFT and SEARCH Functions

Another way to extract the URL is by combining a few other Excel functions. This method takes a bit more setup, but it’s still quite manageable:

1. Again start with your hyperlink in a cell, such as A2
2. In another cell, type this formula:
   =RIGHT(A2,LEN(A2)-SEARCH(““,SUBSTITUTE(A2,”“,”“,LEN(A2)-LEN(SUBSTITUTE(A2,”“,””)))))
3. Press Enter to get the URL in the result cell

Here’s a breakdown of what this more complex formula is doing:

Essentially, this looks for the last “*” in the hyperlink, which comes right before the URL portion, and extracts everything after that.

Method 3: Converting the Hyperlink to Plain Text

One other easy method is to convert the hyperlink to plain text:

1. Right-click the cell with the hyperlink
2. Select Remove Hyperlink from the menu
3. The URL will now be displayed as plain text in the cell

This removes the hyperlink formatting, so you just get the raw URL. The downside is that you lose the hyperlink functionality.

Dealing with Relative vs Absolute URLs

One thing to keep in mind is that Excel stores relative URLs differently than absolute URLs:

• Absolute URL – Includes the full web address, like “https://www.example.com”
• Relative URL – Just has the path after the domain, like “/example.html”

The formulas we covered assume the hyperlink contains the absolute URL. For a relative URL, the formulas will return the Excel file path instead of the URL.

To use the formulas with relative URLs, you’ll need to first convert the URLs to the absolute form. Here are the steps:

1. Select your hyperlinks
2. Click File > Info > Edit Links to Files
3. In the dialog box, click Break Link
4. Click File > Info > Check for Issues > Check Compatibility
5. Select the Convert to Absolute URL radio button
6. Click OK

Now your relative URLs will be converted to absolute URLs, and the formulas will work as expected.

Extracting URLs from Multiple Cells

What if you have an entire column of hyperlinks and want to extract all their URLs? You could copy the URL extraction formula down the column, but there’s an easier way using array formulas.

To extract URLs from a whole column:

1. Select a blank cell next to the first hyperlink in your list
2. Type =HYPERLINK(
3. Click the first hyperlink cell and drag down to the last hyperlink to select the whole column
4. Type ) and press Ctrl+Shift+Enter to make it an array formula

The URLs for the entire column should automatically fill in next to your hyperlinks. This saves you from having to copy or drag formulas.

Combining URLs with Other Data

Extracting the URL is often just the first step. Typically, you’ll want to combine it with other page data, like the page title, for further analysis.

To add more data:

1. Add columns for the data you want, like “Page Title”, “H1”, “Status”
2. Use HYPERLINK to extract the URL in the first column
3. Fill in the other columns manually or with web scraping tools as needed

Some common uses for this:

• Building a list of links to audit
• Analyzing link equity for SEO
• Tracking 404 errors and redirects during a site migration

Basically any time you need a quick way to get a list of URLs from hyperlinks, these techniques will come in handy.

Troubleshooting

If you’re having trouble getting the URL from your hyperlink, here are a few things to double check:

• Make sure the cell actually contains a hyperlink and not just plain text
• Check that the hyperlink is formatted correctly, starting with “https://” or “http://”
• Ensure you’ve entered the formula correctly, including the cell reference
• Try both the HYPERLINK and RIGHT/LEFT/SEARCH methods to see if one works better
• If using a relative URL, convert it to an absolute URL first

Extracting URLs can be a bit tricky, but with these troubleshooting tips you should be able to resolve any issues.

Final Thoughts

Excel hyperlinks have a lot going on under the hood, but extracting just the URL is surprisingly easy once you know how. Whether you use the HYPERLINK function, RIGHT/LEFT/SEARCH functions, or simply convert the link to plain text, you can quickly get the raw URL from any hyperlink.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.