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](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/02/Extract-URLs-from-Hyperlink-Text-in-Google-Sheet-1-min.png?w=1200)
Go to Apps Script
![Extract URLs from Hyperlink Text in Google Sheet](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/02/Extract-URLs-from-Hyperlink-Text-in-Google-Sheet-2-min.png?w=1200)
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](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/02/Extract-URLs-from-Hyperlink-Text-in-Google-Sheet-3-min.png?w=1200)
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](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/02/Extract-URLs-from-Hyperlink-Text-in-Google-Sheet-4-min.png?w=1200)
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](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/02/Google-sheet-url-id-example.png?w=1200)
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](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/02/Extract-URLs-from-Hyperlink-Text-in-Google-Sheet-6-min.png?w=1200)
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](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/02/extract-link-from-hyperlinks-in-google-sheet-PWD-BLOG-1220630-4-min.png?w=1200)
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](https://i0.wp.com/prettywingsdigital.com/wp-content/uploads/2022/04/SKS-Pics-9.png?fit=612%2C612&ssl=1)