从 Excel 超链接公式中提取 URL [英] Extract URL From Excel Hyperlink Formula

问题描述

我有一个包含数百个单元格的 Excel 文件，这些单元格使用超链接公式 =HYPERLINK( <targetURL>, <friendlyName> ).我需要从中提取纯文本 URL.我发现的大多数示例都依赖于使用不同超链接方法的单元格.

I have an Excel file with hundreds of cells that use the Hyperlink formula =HYPERLINK( <targetURL>, <friendlyName> ). I need to extract the plain text URLs from these. Most examples that I've found rely on the cell using a different hyperlinking method.

这样的函数:

Function HyperLinkText(pRange As Range) As String

   Dim ST1 As String
   Dim ST2 As String

   If pRange.Hyperlinks.Count = 0 Then
      HyperLinkText = "not found"
      Exit Function
   End If

   ST1 = pRange.Hyperlinks(1).Address
   ST2 = pRange.Hyperlinks(1).SubAddress

   If ST2 <> "" Then
      ST1 = "[" & ST1 & "]" & ST2
   End If

   HyperLinkText = ST1

End Function

导致单元格文本未找到".或者，有没有办法将这些单元格转换为其他超链接格式，以便我拥有的宏可以工作?

results in cell text "not found". Alternatively, is there a way of converting these cells to the other hyperlink format so that the macro I have works?

推荐答案

这里有一个方法可以返回超链接文本，无论它是由公式创建的，还是由插入/超链接方法创建的.

Here is a method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.

如果是前者，我们只需要解析公式即可；如果是后者，我们需要遍历工作表上的超链接集合.

If the former, we merely have to parse the formula; if the latter, we need to iterate through the hyperlinks collection on the worksheet.

如果cell_ref中没有超链接，公式将不返回任何内容；改变以适应.

The formula will return nothing if there is no hyperlink in cell_ref; change to suit.

Option Explicit
Function HyperLinkText(rg As Range)
    Dim sFormula As String, S As String
    Dim L As Long
    Dim H As Hyperlink, HS As Hyperlinks

sFormula = rg.Formula
L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare)

If L > 0 Then
    S = Mid(sFormula, L + 11)
    S = Left(S, InStr(S, """") - 1)
Else
    Set HS = rg.Worksheet.Hyperlinks
    For Each H In HS
        If H.Range = rg Then
            S = H.Address
        End If
    Next H
End If

HyperLinkText = S

End Function

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