7 Ways to Extract the URL from a Hyperlink in Microsoft Excel

Do you want to know how to extract the URL from a hyperlink in Microsoft Excel?

This post is going to show you all the ways you can get the URL from a hyperlink in Microsoft Excel!

A hyperlink is a link that allows users to navigate to a web page or document. Hyperlink cells typically contain a URL, which is the address of the linked page or document, and anchor text, which is the text that appears on the screen and is clicked by the user.

When the hyperlink uses anchor text, the actual URL isn’t easily accessible in Excel but you may need this data for other purposes.

Follow this easy step-by-step guide on how to extract the URL from a hyperlink in Microsoft Excel.

Extract the Hyperlink URL with a Click

This is the most obvious method to get the URL from any hyperlink.

When you click on the hyperlink, Excel will launch your internet browser and take you to the website URL.

You can then copy and paste the URL from the browser address bar back into Excel.

Highlight the URL and press Ctrl + C to copy, then go back to Excel and press Ctrl + V to paste the URL into a new cell.

Unfortunately, this is a very manual approach that involves switching back and forth between two applications. There are definitely easier methods!

Extract the Hyperlink URL from the Insert Menu

A slightly easier way to get the URL from your hyperlinks is through the Link command found in the Insert menu.

Follow these steps to extract the URL from the Link command.

  1. Select the cell that contains your hyperlink.
  2. Go to the Insert tab in the ribbon.
  3. Click on the Link command found in the Links section.

This will open the Edit Hyperlink menu which contains both the anchor text and the URL address data.

  1. Highlight the URL from the Address input in the Edit Hyperlink menu and press Ctrl + C to copy.
  2. Press Esc on your keyboard or press the Cancel button to close the Edit Hyperlink menu.
  3. Select a new cell and press Ctrl + V to paste the URL into Excel.

You now have the URL from the hyperlink!

Extract the Hyperlink URL with a Right Click

You can also access the Edit Hyperlink menu with a right-click to avoid using the ribbon commands.

  1. Select the cell from which you want to extract the URL.
  2. Right click and choose Edit Hyperlink from the options.

This will open the Edit Hyperlink menu and you can copy and paste the URL from the Address as before.

Extract the Hyperlink URL with a Keyboard Shortcut

Using a keyboard shortcut to open the Edit Hyperlink menu is slightly quicker than the previous methods for getting the URL.

This is my favorite method when I only need to extract a couple of URLs from their hyperlinked cells.

Select the cell containing the hyperlink and press Ctrl + K to open the Edit Hyperlink menu.

This will open the Edit Hyperlink menu and you can copy and paste the URL from the Address just like before.

Extract the Hyperlink URL with VBA

If you work with hyperlinks in Excel, you know that it can be a tedious task to extract the URL from each hyperlink.

To save yourself time and effort, you can use VBA to automate the process.

With just a few lines of code, you can extract the URL from multiple hyperlinks in Excel.

Not only does this save you time, but it also ensures the results are accurate as manual processes tend to lead to errors.

Follow these steps to create a VBA macro to extract the URL from your hyperlinks.

  1. Press Alt + F11 to open the Visual Basic Editor (VBE).
  2. Click on the Insert tab of the VBE.
  3. Select Module from the options.
Sub ExtractURL()

Dim rng As Range

For Each rng In Selection
    rng.Offset(0, 1).Value = rng.Hyperlinks(1).Address
Next rng
End Sub
  1. Paste the above VBA code into the code window.

The code will loop through all of the hyperlinks in the selected range and extract the URL from each into the adjacent cell.

Now you can extract URL’s from multiple hyperlinks!

Follow these steps to extract multiple URL’s using the VBA macro.

  1. Select the range of cell from which to extract the URL.
  2. Press Alt + F8 to open the Macro dialog menu to select and run the macro.
  3. Select the ExtractURL macro.
  4. Press the Run button.

The code will run and place the URL in the adjacent cell!

Just make sure the adjacent cells don’t already contain any data or formulas as the code will overwrite anything in the cells directly to the right.

Extract the Hyperlink URL with a Custom Function

Another way you can leverage VBA to extract the URL is through a custom function.

This will allow you to get a dynamic solution, so if the hyperlinks change, the extracted URL will also update accordingly.

Add the above code into the VBE.

Function GetURL(cell As Range)
    GetURL = cell.Hyperlinks(1).Address
End Function

This code will create a custom function that returns the URL from a hyperlink. This custom function can then be used in your worksheet just like any other function.

=GetURL(B2)

Insert the above formula into your worksheet and it will return the URL from the referenced hyperlink.

Extract the Hyperlink URL with Office Scripts

VBA isn’t the only way to automate your work in Excel!

You can also use Office Scripts. It’s a TypeScript based language available in business plans of Microsoft 365 with Excel Online.

Follow these steps to create an Office Script that extracts the URL from your hyperlinks.

  1. Open your workbook in Excel Online.
  2. Go to the Automate tab in the ribbon.
  3. Click on the New Script command.

This will open the code editor on the right hand side.

function main(workbook: ExcelScript.Workbook) {

	//Create a range object from selected range
	let selectedRange = workbook.getSelectedRange();
	//Create an array with the values in the selected range
	let selectedValues = selectedRange.getValues();
	//Get dimensions of selected range
	let rowHeight = selectedRange.getRowCount();
	let colWidth = selectedRange.getColumnCount();

	//Loop through each item in the selected range
	for (let i = 0; i < rowHeight; i++) {
		for (let j = 0; j < colWidth; j++) {	
			let currHyperlink = selectedRange.getCell(i, j).getHyperlink().address;
			selectedRange.getOffsetRange(0, colWidth).getCell(i, j).setValue(currHyperlink);
		};
	};
};
  1. Paste in the above code in the Code Editor.
  1. Rename the script to Extract URL.
  2. Click the Save script button.

Now you will be able to extract the URL’s from any range of hyperlinked cells.

  1. Select the range of cells containing the hyperlinks from which you want to extract the URL.
  2. Press the Run button in the Code Editor.

This Office Script will loop through the selected range and place the URL from the hyperlink into the adjacent cell.

Conclusions

If you work with hyperlinks in Excel, you may sometimes need to extract the URL from those hyperlinks.

There are many different ways to extract the URL from a hyperlink in Microsoft Excel. The most straightforward way is to either click the link and get the URL in the browser or use the Edit Hyperlinks menu.

This is ok when you only need to get the URL from a few hyperlinks and it’s an activity you won’t be doing frequently.

Otherwise, you will need to automate the process to avoid the tedious work involved!

Thankfully, you can automate URL extraction with VBA or Office Scripts in order to get URL’s from multiple hyperlinks.

Extracting the URL from a hyperlink in Excel is a quick and easy process, and the VBA or Office Script solutions will save you tons of time and effort when working with a lot of hyperlink data.

Give it a try next time you need to extract a URL from a hyperlink in Excel! Let me know in the comments section below how it goes!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Related Posts

Comments

5 Comments

  1. Noxo

    There’s one very important caveat to the VBA code: if the URL contains “#”, everything after that character will be returned by the SubAddress property, and will then have to be concatenated.

    Reply
    • John MacDougall

      Thanks for the tip!

      Reply
  2. Nimit

    Get Local URL
    if Function GetURL on Module Drive C:\ can get full file name correct. Show Drive + path + File name
    if Function GetURL on Module Drive D:\ it incorrect show ../../path + File name
    How can solve problem

    Function GetURL2(cell As Range)
    Dim Ob As Object
    Set Ob = cell.Range("A1").Hyperlinks(1)
    GetURL2 = Ob.Address & Ob.SubAddress
    End Function

    =GetURL2(

    Reply
  3. Eric Moss

    Really good stuff! Very useful! One item I have encountered: If a cell does not have a hyperlink, then the VBA script generates an error. I am not a VBA expert (but I have written code in other languages). However, I believe that there is an iferror function of some kind that would skip the cell in the range without a hyperlink.

    Reply
    • John MacDougall

      Yes, you could wrap this in an IFERROR function to avoid the error.

      Reply

Leave a Reply to John MacDougall Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃