☰

Excel の数式を使用して、セル内のハイパーリンクのリンク位置を抽出できますか?

tag-icon • tag-icon microsoft-excel hyperlink
Excel の数式を使用して、セル内のハイパーリンクのリンク位置を抽出できますか?

ハイパーリンクの場所とは異なる表示テキストを持つハイパーリンクを含むセルが多数あるスプレッドシートがあります

例:

セル位置: A1

表示テキスト = "サイト情報"

ハイパーリンクの場所 = "http://www.mylocation.com「

ハイパーリンクの場所のテキスト文字列にアクセスできる Excel 数式はありますか?

理想的には次のようになります。

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

答え1

単一のセルの値からアドレスを抽出するだけが必要だったので、この小さな関数が便利だとわかりました。

「ブルート フォース」マクロの代わりに、指定されたハイパーリンクの URL を抽出して返すユーザー定義関数を作成することもできます。

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

この場合、任意の場所に配置できます。たとえば、A1 のハイパーリンクの URL をセル C25 に表示したい場合は、セル C25 に次の数式を入力します。

=GetURL(A1)

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

答え2

マクロを使用できます:

  • 新しいワークブックを開きます。
  • VBAを起動する（Alt+F11を押す）
  • 新しいモジュールを挿入します（挿入 > モジュール）
  • 以下のExcelユーザー定義関数をコピーして貼り付けます。
  • VBA を終了する (Alt+Q を押す)

  • このカスタム Excel 関数には次の構文を使用します: =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

答え3

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

}

答え4

@SALと同じ問題がありました

オブジェクトは、区切り文字に遭遇すると、.Hyperlink実際にアドレスを分割することがわかりました。「#」はそのような区切り文字の 1 つです (他にもあるかどうかはわかりません)。.Address.SubAddress

上記の @gilly3 のコメントと、この他の StackOverflow の質問を参照してください。 hyperlinks(1).address は部分的なアドレスのみを返す

また、Microsoft の (不完全な) .SubAddress に関するドキュメント: Hyperlink.SubAddress プロパティ

どこにも言及されていないことの 1 つは、どちらにも「#」区切り文字が含まれていないことです。その.Addressため.SubAddress、完全な URL として機能するアドレスが必要な場合は、「#」区切り文字を再度追加する必要があります。

そこで、私は @Matthew Lock のコードを次のように変更し、@Igor O からの複数のハイパーリンクが保存される可能性と、私が追加した「#」も含めました。

 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

これにより、すべてのハイパーリンクの完全なテキスト文字列が返されます (最後にスペースを追加したので、複数のハイパーリンクがある場合は区切られます)。

関連情報