如何使用公式从 Google 表格中的链接中提取 URL? [英] How to extract URL from Link in Google Sheets using a formula?

问题描述

我从网站上复制了一系列超链接并将它们粘贴到 Google 表格中.这些值显示为链接文本，而不是超链接公式，并且仍然正确链接.对于每一行，我试图仅提取 URL(而不是友好文本)并将其插入到相邻的列中.这怎么能用公式来完成?

I have copied from a website a series of hyperlinks and pasted them in a google sheet. The values show up as linked text, not hyperlink formulas, and are still linked correctly. For each row, I'm trying to extract the URL ONLY (not the friendly text) and insert it into the adjacent column. How could this be accomplished using a formula?

例如:

=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly)

这个和类似的场景 不适用，因为粘贴的数据不是公式.我会接受脚本 (GAS) 解决方案，或任何与此相关的解决方案，但如果可以使用公式来完成，我会更喜欢.我发现了几十个 HYPERLINK 操作脚本，但在这个特定场景中没有任何内容，甚至没有找到如何访问持有该 url 的属性.谢谢.

This and similar scenarios do not apply because the pasted data are not formulas. I will accept a script (GAS) solution, or any solution for that matter, but would prefer if it could be done using a formula. I have found dozens of HYPERLINK manipulation scripts, but nothing on this particular scenario, or even how to access the property that is holding that url. Thanks.

推荐答案

您可以使用 importxml 拉入整个数据表及其内容:

You can use importxml to pull in the whole data table and it's contents with this:

=IMPORTXML(A1,"//tr")

在它旁边我们拉入标签中的 url 字符串

next to it we pulled in the url strings in the tags

=IMPORTXML(A1,"//tr/td[1]/a/@href")

最后将字符串与原始域连接起来以创建超链接

and finally concatenated the strings with the original domain to create your hyperlinks

=ARRAYFORMULA("http://www.bnilouisiana.com/"&INDIRECT("A2:A"&COUNTA(A2:A)))

这篇关于如何使用公式从 Google 表格中的链接中提取 URL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！