logo标签列表

如何从包含HYPERLINK的字段中获取URL文本

google-sheets
11

我有一个包含超链接公式的列，例如：

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

我想要添加一列，只包含第一列中的URL（以文本形式呈现），例如：

http://example.com

是否有一种功能可以从超链接中提取网址？我也考虑过在最后一列中使用SPLIT/SUBSTITUTE获取第一列的公式文本并将其剪切，但我不确定是否可以将一个字段代码放入另一个字段。

6个回答
6

试试这些公式

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

例子

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

结果是

A2=http://example.com
1
太好了。谢谢。 - Marjorie Roswell
3

我刚刚创建了这个脚本并且它可以正常工作。

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;


}
这个脚本可以在没有引号的情况下运行。 - undefined
2
你可以使用应用脚本创建自定义函数来实现此目标。尝试以下操作：
  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");
  }

}

这将创建一个自定义函数。在您的Google表格中，您可以像使用任何其他函数一样使用此函数；但是，有一个注意事项，就是您必须将单元格放在引号中，例如：

=EXTRACT_URL("A1")

为了让引用更加方便，上述脚本还支持范围：
=EXTRACT_URL("A1:B10")

希望这对你有所帮助！
无法使其正常工作。在我的情况下，文本中有超链接。 - helsont
1

您可以使用Google Apps Script和宏、菜单和自定义函数的组合来从单元格公式中提取超链接的值。

以下是其工作原理：

  1. 打开Google Sheets文档
  2. 转到“工具”>“脚本编辑器”...
    将打开一个新的浏览器窗口或选项卡
  3. 在Code.gs中突出显示并删除所有代码
  4. 用this Pastebin中的内容替换Code.gs中的所有内容，该内容来自此Google Docs帮助论坛主题
  5. 通过转到“文件”>“保存”保存更改
  6. 单击“运行”>“onOpen”以运行此新脚本
  7. 提示授权脚本时，请授权
  8. 返回到您的Google Sheets文档
    您会看到在“帮助”之后创建了一个名为“提取”的新菜单项
  9. 选择包含=HYPERLINK函数的电子表格中的单元格、行或列
  10. 单击提取->用文本字符串替换公式以启动脚本
宏将递归地提取超链接函数的URL，仅留下值。
注意：在进行任何更改之前，请始终备份您的工作。
我建议在副本电子表格上执行此任务，或者至少在原始单元格的副本上执行此任务，以防出现任何问题，或者如果您希望保留原始单元格的内容或格式。
1
一种方法是复制包含公式的列（假设您想保留它们），然后删除“多余”的内容。可以使用“编辑”>“查找和替换...”来实现删除，每次都将替换为留空：

左侧部分：

查找 =HYPERLINK("并勾选在公式中搜索

（如果您想断开链接，请在*替换为*中放置'。）

右侧部分：

查找 ".+并勾选使用正则表达式搜索和在公式中搜索。

我的谷歌表格版本在查找和替换中没有“在公式中搜索”选项。试图在包含超链接的列中替换文本会出现“注意：无法在公式单元格中替换文本。” - Pawel Markowski
0

您可以使用FORMULATEXT获取超链接公式单元格的文本，然后使用正则表达式提取URL。

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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接