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

问题描述

我有一个Excel文件，其中有数百个单元格使用超链接公式 = HYPERLINK（< targetURL>，< friendlyName>）。我需要从中提取纯文本网址。我发现的大多数例子都是使用不同的超链接方法。

所以这样的功能：

函数HyperLinkText（pRange As Range）As String

Dim ST1 As String
Dim ST2 As String

If pRange.Hyperlinks.Count = 0然后
HyperLinkText =未找到
退出函数
结束如果

ST1 = pRange.Hyperlinks（1）.Address
ST2 = pRange.Hyperlinks（1）.SubAddress

如果ST2 < 然后
ST1 =[& ST1& ]& ST2
结束如果

HyperLinkText = ST1

结束函数

导致单元格文本未找到。或者，是否有一种将这些单元格转换为其他超链接格式的方法，以便我可以使用宏？

解决方案

一种将返回超链接文本的方法，无论是由公式创建还是通过插入/超链接方法创建。

如果是前者，我们只需要解析式;如果是后者，我们需要遍历工作表上的超链接集合。

如果cell_ref中没有超链接，公式将不返回;

---

  Option Explicit 
功能HyperLinkText（rg As范围）
 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）
 
如果L> 0然后
 S =中（sFormula，L + 11）
 S = Left（S，InStr（S，） -  1）
 Else 
设置HS = rg.Worksheet.Hyperlinks 
对于每个H在HS 
如果H .Range = rg然后
 S = H.Address 
结束如果
下一个H 
结束如果
 
 HyperLinkText = S 
 
结束功能
  

---

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.

So a function like this:

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.

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屋！