Extract URLs in Google Sheets Without Script [Step-by-Step Guide]

Without using Google Apps Script, you can extract URLs in Google Sheets.

It doesn’t matter whether the URL is linked to a label in a cell using the Hyperlink function, Insert menu “Insert link”, or copied from any website or blog page.

This method works even after the latest Google Sheets update that brought multiple Hyperlink feature. But using this method it’s not possible to extract multiple URLs from a single cell in Google Sheets.

The below screenshot (GIF) shows the URLs that we want to extract in the range A1:A. The extracted URLs are in C1:C. I have extracted the URLs without any App Scripts in Google Sheets!

In the above example, the cell A1 contains the link with the label copied from this blog.

In cell A2 and A3, I have hyperlinks. I mean I have used the Hyperlink function to link a label with URL.

So when you go to cell A1, you won’t see the URL on your Google Sheets formula bar. There is no formula to show. But on the other two cells, you will see the hyperlink formula.

If you use the menu Insert > Insert link to insert a hyperlink in cell A4, that link would behave just like the link in cell A1.

I mean you won’t see any formula in the formula bar (this started happening after the recent Hyperlink update).

From these three types of hyperlinks in cells, you can extract or separate the URLs in Google Sheets that without using any Apps Script.

How to extract the URLs in Google Sheets without any script or custom functions? Please follow the step-by-step instructions below.

How to Extract URLs in Google Sheets from Hyperlinks

Actually, there is no built-in function in Google Sheets that can extract URLs from a hyperlink in a cell.

There is one formula, but that will only work with the hyperlinks in cell A2 and A3, that is inserted via the Hyperlink function.

Here is that formula for your reference.

=REGEXEXTRACT(FORMULATEXT(A2),"""(.*)"",")

With the following workaround, that even novice Google Sheets users can follow, we can extract URLs from any type of (the said three above) hyperlinks. Here are the steps involved.

Step-By-Step Instructions to Separate URLs from Hyperlink

The above hyperlinks are in “Sheet1” (tab name) column A. I am going to extract the URLs in “Sheet2” (tab name).

Step 1: Copy Contents (Hyperlinks) to “Sheet2”

In “Sheet2” in cell A1 use the below formula to copy the contents (hyperlinks) available in “Sheet1” column A.

={Sheet1!A1:A}

If there are blank cells between the hyperlinks, then instead of the above simple formula, use the below Filter formula.

=filter(Sheet1!A1:A,Sheet1!A1:A<>"")

Step 2: Publish the Tab “Sheet1”

Go to the File menu and click on “Publish to the web” (later you can unpublish if you have confidential info in your sheet).

Under Link, select tab name, i.e. “Sheet1”, and then select “web page”.

Check “Automatically republish…” and click the “Publish” button.

Once published, you will get a link URL. Copy that.

Step 3: Import Published Google Sheets Contents Back to Google Sheets

In cell E1 in “Sheet2” enter the below formula (column E is my helper column).

=IMPORTXML("URL","//a/@href")

Replace the URL in this formula with the just copied (published) URL.

In cell C1 just enter the below SUBSTITUTE formula and voila!

=ArrayFormula(
     if(len(A1:A),
     SUBSTITUTE(
        mid(E1:E,1,search("&sa",E1:E)-1),
        "https://www.google.com/url?q=",
        ""
     ),
     )
)

Additional Notes

When you add more links to column A in “Sheet1” and want to extract that URLs too, unpublish the sheet, if already not. Then publish again to get the link.

Delete the existing IMPORTXML formula. Copy the formula under Step # 3 above (without URL) and again replace the text “URL” with the copied (published) URL.

How to Extract Link Labels in Google Sheets

You have successfully extracted the link URLs in Google Sheets, right? How to separate the link labels from hyperlink then?

We have the hyperlinks in Column A. In cell B1, apply the below ArrayFormula. This will extract the link labels.

separate hyperlink label in Google Sheets

=ArrayFormula(A1:A&"")

That’s all about extracting URLs from hyperlinks without Apps Script in Google Sheets.

Resources:

15 COMMENTS

Kevin Michael Valenzuela March 2, 2021 At 6:05 am

You’re the best at this bro. Sending love from the Philippines.

sib October 14, 2020 At 6:51 pm

Thanks !

Jessa Mae Deloy August 6, 2020 At 8:54 am

This is really helpful! Thank you, Prashanth.

bahar May 21, 2020 At 5:55 pm

Hi Prashanth,

I was looking to get the ‘Sheet3’ url into a cell A1 with only formula (without script).

Thank You.

- Prashanth May 23, 2020 At 7:57 am
  
  Hi, Bahar,
  
  I don’t find any supporting function in Google Sheets.
  
  As you may know, manually we can do that with right-clicking on Sheet3!A1 and click “Get link to this cell”.
  
  - bahar May 23, 2020 At 12:28 pm
    
    Hi Prashanth,
    
    Thank you for your response, have a great day.
    
    Thank You.
    
Wayne May 11, 2020 At 9:17 pm

This worked, thank you!

(Note: It does not work if you’re signed in to your company’s Gsuite account. Make sure to sign in to your personal Google account before starting the steps).

- Prashanth May 12, 2020 At 8:44 am
  
  Hi, Wayne,
  
  Thank you for your valuable feedback.
  
- Misha October 22, 2020 At 11:14 am
  
  Is there a way to get this to work without unrestricted publishing (i.e., within a company G-Suite account)?
  
Hamza August 20, 2019 At 6:38 pm

Thanks man really helpful

Ben August 13, 2019 At 11:03 pm

Amazing – Thank you for sharing!

Pat M February 14, 2019 At 4:03 am

Prashanth,

So, I tried a couple of different things. The sheet I was trying to copy had around 1900 rows of URLs.

Seems like the largest number of rows it will work with is around 1000

To get it to work, I had to break it up into two sheets and just run the process twice.

Pat

- Prashanth February 14, 2019 At 7:27 am
  
  Hi,
  
  I think that’s the case.
  
  There are similar issues with all the text functions like, join, textjoin, ampersand sign, concatenate etc.
  
  Best,
  
Pat M February 13, 2019 At 12:21 am

Prashanth,

I did everything as outlined above for Google Sheets. When I went to Step 3 it returned the following Error.

“Resource at URL Contents Exceeded Maximum Size”

Thoughts?

Pat

- Prashanth February 13, 2019 At 8:12 am
  
  Hi, Pat,
  
  Actually, I was unaware of that issue due to my testing with a limited dataset.
  
  Thanks for the info.
  
  You can try the following workaround.
  
  Remove unwanted rows and columns in your Sheet (published). The aim is to reduce the file size. Then try.
  
  If that doesn’t work, try to import a limited number of rows.
  
  Existing Formula.
  
  =IMPORTXML("URL","//a/@href")
  
  Play around with;
  
  =Array_Constrain(IMPORTXML("URL","//a/@href"),500,1)
  
  This formula imports 500 rows and 1 column.
  
  But I didn’t test any of the above workarounds.
  
  Thanks for your understanding.
  
  Best