Extract URLs from Hyperlink Text in Google Sheet

Extract URLs from Hyperlink Text in Google Sheet

Spread the love

To extract URLs from hyperlinks there used to be few tools on Google Sheet but they are not available anymore. But do not worry as in this article I will show you the easiest by which you can separate links and text from the hyperlink text in Google Sheet.

The trick is simple and 100% working

How to separate links from Text in Google Sheet

Here is a sample google sheet file. You see that there is a list of 12 hyperlinks. When you try to copy and see the paste option you will see ‘paste values only’ in the option and not ‘paste links only’. So with simply copy-paste, the links cannot be separated.

To do so we will use a script to create a function that will extract the links from the data in column A and paste them in column B.

Go to the ‘Extensions’ tab

Extract URLs from Hyperlink Text in Google Sheet

Go to Apps Script

Extract URLs from Hyperlink Text in Google Sheet

You will see a few codes in the Code.gs section. Delete all the previous code. Now it will look like this.

Extract URLs from Hyperlink Text in Google Sheet

Now copy the below mentioned code and paste it on the code.gs section as shown below

var your_sheet_id = '';
var your_sheet_name = '';
var ss = SpreadsheetApp.openById(your_sheet_id);
var report = ss.getSheetByName(your_sheet_name);
function convertLinks() {
  var header = Array.from(report.getRange(1, 1, 1, report.getLastColumn()).getValues())[0];
  var rows = report.getLastRow();
  var cols_with_links = header.map((i,n,r) => { //i == the current value on the iteration. n == the index, r == the full array/header
    let links = Array.from(report.getRange(1,(n+1),rows,1).getRichTextValues()).map(row=> [row[0].getLinkUrl()]);
    links.shift();
    let head = [[('URL FOR '+i)]];
    let new_col = [...head,...links];
    return {
      col_index: n,
      has_links: links.some(v=> v[0]),
      new_col: new_col
    }
  }).filter(r=> r.has_links);
  cols_with_links.forEach((itm,i,r)=> {
    let target_col_index = itm.col_index+i+2;
    report.insertColumnsAfter((itm.col_index+i+1),1);
    report.getRange(1,target_col_index,itm.new_col.length).setValues(itm.new_col);
  })
}

Now the code will look like the image below.

In the first row, we need to enter the sheet id within the inverted comma. Go to your Google Sheet and copy the URL.

Extract URLs from Hyperlink Text in Google Sheet

The URL will look like this. The Sheet ID is the code highlighted in yellow. Copy this code from your URL and paste into the script code.

Google-sheet-url-id-example

Next, you need to copy the sheet name and paste it within the inverted comma. Once you are done with the Sheet Id and Sheet Name then save the script and then click on the Run tab.

Extract URLs from Hyperlink Text in Google Sheet (6)-min

Now, it will ask you to sign in with the same google account and give permission.

Once it’s done, you can see the sheet. You will see that all the links are extracted and pasted in column B.

extract link from hyperlinks in google sheet PWD BLOG 1220630 (4)-min

Let us know your thoughts on this article on “Extract URLs from Hyperlink Text in Google Sheet” in the comments section below👇

digitalsunilsah Sunil Kumar Sah
DIGITALSUNILSAH

Sunil @digitalsunilsah is the founder of Prettywings Digital. He is a Digital Marketer, YouTuber  and Ecommerce Seller. He is very passionate digital entrepreneur who loves blogging on Online Marketing and eCommerce. Follow Sunil on Social Media:

digitalsunilsah

Blogger | Ecommerce Seller | YouTuber

Leave a Reply