How to get url text out of field with HYPERLINK in it

Question

I have a column with hyperlinks formula in it, for example:

=HYPERLINK("http://example.com", "Link")

I want to get additional column, where only urls (as a text) from the first column will be present, that is, in this example:

http://example.com

Is there a function that allows extraction of an url from HYPERLINK? I was thinking also about getting formula text from the first column and cutting it with SPLIT/SUBSTITUTE in the final one, but I'm not sure if I can get one field code into another field.

score 6 · Answer 1 · answered Jun 06 '19 at 00:57

6

Try this formulas

A2=index(SPLIT(SUBSTITUTE(FORMULATEXT(A1),"=HYPERLINK(""",""),""","""),1,1)

example

A1=HYPERLINK("http://example.com", "Link")

result is

A2=http://example.com

answered Jun 06 '19 at 00:57

mohagali

401
4
10

1

That's great. Thanks. – Marjorie Roswell Sep 22 '19 at 14:25

score 2 · Answer 2 · answered Jun 20 '16 at 19:12

You can accomplish this by using Apps Script to create a custom function. Try this:

Open your Google Sheet.
In the menu bar, open Tools > Script editor...

In Code.gs, paste the following and save:

function EXTRACT_URL(input) {

  var range = SpreadsheetApp.getActiveSheet().getRange(input);
  var re = /^.+?\(\"(.+?)\",.+?$/;
  if (input.indexOf(':') != -1) {
    var formulas = range.getFormulas();
    for (var i in formulas) {
      for (var j in formulas[i]) {
        formulas[i][j] = formulas[i][j].replace(re, "$1");
      }
    }
    return formulas;
  } else {
    return range.getFormula().replace(re, "$1");
  }

}

This creates a custom function. In your Google Sheet, you can use this function as you would use any other function; however, there's one caveat, which is that you'll have to put the cell in quotes, e.g.:

=EXTRACT_URL("A1")

To make the quotes thing less of a hassle, the above script supports ranges as well:

=EXTRACT_URL("A1:B10")

Hope this helps!

wasn't able to get this to work. the text had the hyperlink in my case — helsont, Jul 18 '16 at 18:49

score 2 · Answer 3 · answered Oct 21 '21 at 14:25

I just created this script and it worked

function URL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  
  try {
    var range = sheet.getRange(args[1]).getRichTextValue().getLinkUrl();
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  return range;


}

score 1 · Answer 4 · answered Feb 12 '15 at 13:48

1

One way would be to copy the column containing the formulae (assuming you would like to retain them) and remove the 'excess'. The removal can be achieved with Edit > Find and Replace... with Replace with left blank in each case:

The part to the left:

Find =HYPERLINK(" and check Also search within formulae

(If you want to break the link, put ' in *Replace with *.)

The part to the right:

Find ".+ and check Search using regular expressions and Also search within formulae.

answered Feb 12 '15 at 13:48

pnuts

58,317
11
87
139

My version of google sheets doesn't have the option "Also search within formulae" in Find and Replace. Trying to replace text in column with HYPERLINKs results in "Note: Text can't be replaced in formula cells." – Pawel Markowski Feb 12 '15 at 15:25

rjb · Answer 5 · 2015-10-21T16:09:30.730

You can use Google Apps Script and a combination of Macros, Menus, and Custom Functions to extract the value of the hyperlink out of the cell's formula.

Here's how it would work:

Open the Google Sheets document
Go to Tools > Script Editor...
A new browser window or tab will open
Highlight and delete all of the code in Code.gs
Replace everything in Code.gs with the contents from this Pastebin, taken from this Google Docs Help Forum topic
Save the changes by going to File > Save
Run this new script by clicking on Run > onOpen
When prompted, authorize the script
Go back to your Google Sheets document
You'll see a new menu item has been created after Help, named "Extract"
Select the cell(s), row(s) or column(s) in your spreadsheet that contain the =HYPERLINK functions
Click on Extract -> Replace formulas with Text strings to initiate the script

The macro will recursively extract out the URL of the hyperlink function, leaving you with just the value.

Note: Always make a backup of your work before making any changes.

I would recommend performing this task on a duplicate spreadsheet or at a minimum a copy of the original cells in case anything should go wrong of if you wish to retain the original cell's content or formatting.

score 0 · Answer 6 · answered Jul 08 '21 at 03:16

0

You can get the text of the hyperlink formula cell using FORMULATEXT, then extract the URL using a regular expression.

=REGEXEXTRACT(FORMULATEXT(A1), "^=HYPERLINK(""([^""]+)")

answered Jul 08 '21 at 03:16

Mark Doyle

4,804
3
20
23

How to get url text out of field with HYPERLINK in it

6 Answers6

Linked