Blog

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

Go to Apps Script

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

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.

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.

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.

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.