I need to change the multiple links in a Google Sheets from normal sheet hyperlinks such as:

into:

<a href="https://traveltractions.com">Travel Tractions</a>

How can I do this in bulk?

• Thanks, I've played with this a little but admittedly I'm not very good at it. Do you know a script I could try or point me to a place where there are already made scripts for this? Surely it's a common problem, I looked for extensions and add-ons but nothing was definitive. – matthew Oct 14 '17 at 19:55
• Can you give an idea of how they're laid out? Are you wanting to bulk change a list of just links, or are they scattered about in your spreadsheet? – David Metcalfe Oct 14 '17 at 22:11
• Maybe an easier solution is to use Find/Replace. Just be sure to check the search inside formulas checkbox – Rubén Oct 15 '17 at 18:03
• @pnuts suggested I change "Also search within formulae:. His formula didn't work but now at least I can do a search and replace. Thank you – matthew Oct 15 '17 at 18:51

Could be impractical if your links are scattered throughout a sheet, but should be viable if say all in a column (assumed A):

• Replace all = in ColumnA with say # with Also search within formulae
• Enter, say in B1, and copy down to suit:

=substitute(trim("<a href="&mid(A1,12,find(",",A1,12)-12)&">"&mid(A1,find(",",A1)+2,len(A1))),char(34)&")","</>")

• Copy ColumnB, Paste special, Paste values only over the top

• Replace all # in ColumnA with = (or delete ColumnA?)

There are several ways to do this. Next, two of them:

1. Using built-in FIND/REPLACE with the find insi

1. Replace =HYPERLINK( by <a href=".
2. Replace ," by >.
3. Replace ") by </>

1. Use the getFormula(range) or getFormulas(range) Google Apps Script's methods to get the formula(s),
2. Use replace(string) JavaScript method

For examples, checkout Extract the link text and URL from a hyperlinked cell

It's fairly rudimentary, but a Google Apps Script solution could be as follows:

function createHref(url, title){
/* Create the URL and return it. */
return '<a href=' + url + '>' + title + '</a>';
}

function flatten(arr) {
/* Flatten n-dimensional array. */
return arr.reduce(function(flat, toFlatten) {
return flat.concat(
Array.isArray(toFlatten) ? flatten(toFlatten) : toFlatten
);
}, []);
}

function setCellVal(row, col, val) {
/* Function to make setValue() less annoying. */
.getRange(row, col)
.setValue(val);
}

/* Main function */
const outputCol = 2; // Column for formatted link output.
const numRows = sh.getLastRow();
const myRegexp = /\("(.*?)".*?\"(.*?)"/g;