CODEWITHSUNDEEP
google-sheets
Pawel Markowski
Pawel Markowski

Reputation: 1196

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

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.

Upvotes: 11

Views: 23048

Answers (6)

Isac Cruz
Isac Cruz

Reputation: 31

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;


}

Upvotes: 3

Mark Doyle
Mark Doyle

Reputation: 4874

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

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

Upvotes: 0

mohagali
mohagali

Reputation: 411

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

Upvotes: 6

Ryan Martin
Ryan Martin

Reputation: 21

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

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

  3. 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!

Upvotes: 2

rjb
rjb

Reputation: 9106

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:

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

Upvotes: 1

pnuts
pnuts

Reputation: 59450

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.

Upvotes: 1

Related Questions