Published on

Extract hyperlink from text in Google Sheets

First of all, in the upper menu bar go to Extensions -> Apps Script.

Then, add the following function (you can safely delete whatever was there if you've never opened Apps Script before):

function GETLINK(input){
	return SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl();
}

Courtesy of MrG on this Stack Overflow question

Back to your sheet, in a new column write =GETLINK(B2) (replace B2 with the column you want to extract the hyperlink from and the row it starts at).

There will be an error, but we will fix it soon.

Now, drag the fill handle (the small square at the bottom-right corner of a selected cell) to autofill your desired cells.

Then select your column and press CTRL + H.

The Find and replace window should open up, alternatively, go to Edit > Find and replace (should be the last option).

Inside the Find input field, paste \(([A-Z][0-9]+)\).

Inside the Replace with input field, paste ("$1").

Check :white_check_mark: Search using regular expressions and :white_check_mark: Also search within formulas.

This will effectively replace every B2, B3 inside your formula with "B2", "B3", making the custom formula GETLINK work.

The new column should be filled with the desired links!

Now, you probably want to copy the links somewhere else and avoid using the formula for them to show up.

First, select the the newly populated column, then go to a new empty column and Right Click > Paste Special > Values Only.

The result will be a column with links as text, but no hyperlink associated with them.

To fix this, in the Apps Script section create a new file convertToHyperlinksCtrlKStyle and paste this code:

function convertToHyperlinksCtrlKStyle() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sourceRange = sheet.getRange('B2:B'); // Source column where URLs are stored
var targetColumn = 2; // Column number for the target column (B is 2, C is 3, etc.)
var values = sourceRange.getValues();

for (var i = 0; i < values.length; i++) {
var cellValue = values[i][0];
if (cellValue && isValidURL(cellValue)) {
var targetCell = sheet.getRange(i + 2, targetColumn); // Adjust row and column
targetCell.setValue(cellValue); // Set the plain URL as the text in the target cell
}
}
}

// Optional function to validate if the text is a URL
function isValidURL(url) {
var regex = /(http|https):\/\/[^\s$.?#].[^\s]\*/;
return regex.test(url);
}

Edit your column B2:B, the source of the text links, and the target column 2 which is the same as B in this case.

After that, click the Run button on the upper menu.

Done! You should see the changes on your sheet.

Remember that this will work on your currently active sheet.

Share: