How to extract URL from Link in Google Sheets using a formula?

google-sheets formula
iamtoc picture iamtoc · Feb 5, 2016 · Viewed 47.9k times · Source

I have copied from a website a series of hyperlinks and pasted them in a google sheet. The values show up as linked text, not hyperlink formulas, and are still linked correctly. For each row, I'm trying to extract the URL ONLY (not the friendly text) and insert it into the adjacent column. How could this be accomplished using a formula?

For example:

=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly)

This and similar scenarios do not apply because the pasted data are not formulas. I will accept a script (GAS) solution, or any solution for that matter, but would prefer if it could be done using a formula. I have found dozens of HYPERLINK manipulation scripts, but nothing on this particular scenario, or even how to access the property that is holding that url. Thanks.

Answer

Jordan picture Jordan · Dec 15, 2019

If your hyperlink is specified in another cell as a formula—for example let's suppose that cell A1 contains the formula =HYPERLINK("https://www.wikipedia.org/","Wikipedia"), you can extract the Link text using a regular expression. All you need to do is:

=REGEXEXTRACT(FORMULATEXT(A1),"""(.+)"",")

This formula will yield the result:

https://www.wikipedia.org/

No custom functions required.

Related questions

How to utilize date add function in Google spreadsheet?

I believe the issue I am having now should be much easier in MS Excel. However, since my company uses Google Spreadsheet so I have to figure out a way. Basically, I have a cell that contains a date value …

Read More
Create in-cell dropdown with filtered range

I'm using Google Spreadsheet. To illustrate my problem, I use the range A2:A8 for the data validation of D2 and E2. But because in cell D2, you are supposed to select an animal only, I'd like to filter the …

Read More
The last & the first day of a month in Google Spreadsheets

What is the way to get the first and last day of the previous month in Google Spreadsheet? I tried several ways to solve this issue with no success.

Read More