☰

Kann ich eine Excel-Formel verwenden, um den Linkstandort eines Hyperlinks in einer Zelle zu extrahieren?

tag-icon • tag-icon microsoft-excel hyperlink
Kann ich eine Excel-Formel verwenden, um den Linkstandort eines Hyperlinks in einer Zelle zu extrahieren?

Ich habe eine Tabelle mit einer großen Anzahl von Zellen, die Hyperlinks enthalten, deren Anzeigetext sich von der Position des Hyperlinks unterscheidet.

dh:

Zellstandort: A1

Anzeigetext = "Site-Info"

Hyperlink-Standort = "http://www.mylocation.com"

Gibt es eine Excel-Formel, mit der ich auf die Textzeichenfolge des Hyperlink-Speicherorts zugreifen kann?

Im Idealfall würde es so aussehen:

FORMEL(A1) = "http://www.mylocation.com"

Antwort1

Ich musste nur die Adresse aus dem Wert einer einzelnen Zelle extrahieren, daher fand ich diese kleine Funktion praktisch:

Anstelle eines „Brute-Force“-Makros könnten Sie auch eine benutzerdefinierte Funktion erstellen, die die URL für jeden Hyperlink, auf den sie verweist, extrahiert und zurückgibt:

Function GetURL(rng As Range) As String
     On Error Resume Next
     GetURL = rng.Hyperlinks(1).Address 
End Function

In diesem Fall können Sie es platzieren, wo Sie möchten. Wenn Sie beispielsweise möchten, dass die URL eines Hyperlinks in A1 in Zelle C25 aufgeführt wird, geben Sie in Zelle C25 die folgende Formel ein:

=URL abrufen(A1)

http://excel.tips.net/T003281_URLs_aus_Hyperlinks_extrahieren.html

Antwort2

Sie können ein Makro verwenden:

  • Öffnen Sie eine neue Arbeitsmappe.
  • Rufen Sie VBA auf (drücken Sie Alt+F11)
  • Neues Modul einfügen (Einfügen > Modul)
  • Kopieren Sie die benutzerdefinierte Excel-Funktion unten und fügen Sie sie ein
  • VBA verlassen (Alt+Q drücken)

  • Verwenden Sie diese Syntax für diese benutzerdefinierte Excel-Funktion: =GetURL(cell,[default_value])

     Function GetURL(cell As range, Optional default_value As Variant)
 'Lists the Hyperlink Address for a Given Cell
 'If cell does not contain a hyperlink, return default_value
      If (cell.range("A1").Hyperlinks.Count <> 1) Then
          GetURL = default_value
      Else
          GetURL = cell.range("A1").Hyperlinks(1).Address
      End If
End Function

Antwort3

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");
  }

}

Antwort4

Ich hatte das gleiche Problem wie @SAL

Ich habe festgestellt, dass das .HyperlinkObjekt die Adresse tatsächlich in .Addressund aufteilt .SubAddress, wenn es auf ein Trennzeichen stößt. „#“ ist ein solches Trennzeichen (ich weiß nicht, ob es noch andere gibt).

Siehe den Kommentar von @gilly3 oben und diese andere StackOverflow-Frage: hyperlinks(1).address gibt nur eine Teiladresse zurück

Auch die (unvollständige) Dokumentation von Microsoft zu .SubAddress: Hyperlink.SubAddress-Eigenschaft

Eine Sache, die nirgends erwähnt wird, ist, dass das Trennzeichen „#“ in keinem der beiden enthalten ist. .AddressWenn .SubAddressSie also eine Adresse wünschen, die tatsächlich als vollständige URL funktioniert, müssen Sie das Trennzeichen „#“ wieder hinzufügen.

Damit habe ich den Code von @Matthew Lock wie folgt geändert und dabei auch die Möglichkeit der Speicherung mehrerer Hyperlinks von @Igor O und meine Ergänzung von „#“ berücksichtigt:

 Function getURL(rng As Range) As String
     On Error Resume Next
     fullURL = ""
     For Each HL In rng.Hyperlinks
    
         If Len(HL.SubAddress) > 0 Then
             fullURL = fullURL & HL.Address & "#" & HL.SubAddress & " "
         Else
             fullURL = fullURL & HL.Address & " "
         End If

     Next

     getURL = fullURL

 End Function

Dies sollte eine Textzeichenfolge mit allen vollständigen Hyperlinks zurückgeben (ich habe am Ende ein Leerzeichen hinzugefügt, damit mehrere Links getrennt werden, falls welche vorhanden sind).

verwandte Informationen