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:
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:
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:
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 here.
The following tutorials explain how to perform other common operations in Excel: