Table of Contents
How do I extract the URL from a hyperlink in Excel?
Answer. The easiest way is to use the MID() function. The first argument is the cell that has the hyperlink. The second is the character number to start at. The third is the number of characters to return. The number of the first character in a cell is 1. The number of the last character in a cell is its length. For example, if cell A1 has the value http://www.google.com, then =MID(A1,9,22) will return google.com. If you don’t know the starting position, you can use the FIND() function. For example, =MID(A1,FIND(“/”,A1,9),22) will also return google.com
If the URL is to an image, then =LEFT(RIGHT(A1,LEN(A1)-FIND(“?”,A1)),FIND(“&”,RIGHT(A1,LEN(A1)-FIND(“?”,A1)))-1) will return the image URL.
You can use the TEXT function in Excel to extract a URL from a hyperlink.
For example, you can use the following formula to extract the URL from the hyperlink in cell A2:
=TEXT(A2, "")
The following example shows how to use this syntax in practice.
Example: Extract URL from Hyperlink in Excel
Suppose we have the following column of hyperlinks in Excel:
If we click on any of these hyperlinks, we will automatically be taken to the URL that the hyperlink points to.
Suppose we would instead like to extract the URL from each hyperlink as text.
We can type the following formula into cell B2 to do so:
=TEXT(A2, "")
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains only the URL from each hyperlink in column A.
If we click on any of the URL’s in column B, we will not automatically be redirected to the the web page in a web browser.
How This Formula Works
Recall the formula that we used to extract the URL from the hyperlink in cell A2:
=TEXT(A2, "")
This formula uses the TEXT function, which uses the following basic syntax:
TEXT(Value you want to format, “Format code to apply”)
By using the formula TEXT(A2, “”) we tell Excel that we’d like to get the value in cell A2 and apply no format to it.
This results in just the text from cell A2 by itself.
Note: You can find the complete documentation for the TEXT function in Excel .