如何使用HYPERLINK在网站中获取url文本 [英] How to get url text out of field with HYPERLINK in it

问题描述

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

  = HYPERLINK（http：// example。 com，Link）
  

我想获得更多列，只有url（作为文本）将会出现，也就是在这个例子中：

  http://example.com 
  

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

解决方案

您可以使用 Google Apps脚本以及宏，菜单和自定义函数的组合可以将超链接的值提取出单元格的公式。

打开Goog​​le表格文档

1. 转到工具>脚本编辑器...
   
   新的浏览器窗口或标签将打开


2. 突出显示并删除<$ c中的所有代码$ c> Code.gs


3. 将 Code.gs 中的所有内容替换为此Pastebin 摘自此Google文件帮助论坛
   主题


4. 通过转到文件>保存
li>

5. 点击Run> onOp运行这个新脚本
   
6. 当出现提示时，授权脚本


7. 返回到您的Google表格文档
   
   您会看到在帮助之后创建了一个名为提取的新菜单项


8. 选择电子表格中包含的单元格，行或列 = HYPERLINK 函数


9. 点击 Extract - >将公式替换为文本字符串以启动脚本

宏将递归地提取超链接的URL功能，只留下值。

注意：在进行任何更改之前，请务必备份您的工作。

如果您希望保留原始单元格的内容或格式，如果出现任何问题，我建议在重复的电子表格或至少原始单元格的副本上执行此任务。

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.

解决方案

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.

这篇关于如何使用HYPERLINK在网站中获取url文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！