ExcelでHyperlinkのURLを抜き出す

概要

ExcelでHyperlink付きのセルからURL (アドレス) のみを抜き出す方法を調べたのでまとめ.

既存の関数だとできないらしく, 自作マクロか自作関数を一時的に用意する必要があるらしい. いずれも開発タブのVisual Basicによって実装可能. 作業が終わったら, 後でそれらのファイルは削除すればマクロ等も残らないのでOK.

サンプルファイルと中身の画像は下表:

	サンプル	中身の画像
変更前	ファイル
変更後	ファイル

実際の手順の動画:

準備

Visual Basicを使うので, 開発タブを表示するように設定しておく

変換

ハイパーリンク付きのテキスト

自作マクロを使う.

• 開発タブ -> Visual Basic をクリックし, Microsoft Visual Basicの編集画面を表示する

• 対象のエクセルを選択しておき,

• 挿入 -> 標準モジュール をクリックし, Moduleの編集画面を開く

• 下記のマクロのコードをコピペ

Sub Extracthyperlinks()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "GetURLAsHyperlink"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Rng.Hyperlinks.Count > 0 Then
        Rng.Value = Rng.Hyperlinks.Item(1).Address
    End If
Next
End Sub

• 実行ボタン を押して, 変換したいセルを選択してOKを押す.

  指定したセルが変換されるので, 元のHyperlink付きのセルを残したい場合は事前にコピーして残しておく必要あり.

  

• 変換できた

テキストのみ

自作関数を使う.

• 開発タブ -> Visual Basic をクリックし, Microsoft Visual Basicの編集画面を表示する

• 対象のエクセルを選択しておき,

• 挿入 -> 標準モジュール をクリックし, Moduleの編集画面を開く

• 下記の関数のコードをコピペ. 関数名はGetURL

Function GetURL(cell As Range) As String
GetURL = cell.Hyperlinks(1).Address
End Function

• GetURL(変換元のセル) で, 変換元のセルのURLをテキストとして抜き出すことができる

  ↓

• オートフィルで他のセルも選択すれば, 変換完了

変換後

今回自作したマクロ, 関数が不要の場合は, Microsoft Visual Basicの編集画面で, 自作マクロ, 自作関数を削除すれば良い.

• 追加した自作マクロ, 自作関数はModule1などの名前になっている

• これを右クリックして, Macなら「Module1の削除」 Windowsなら「Module1の開放」 をクリックし

• 出てくるダイアログで「いいえ」をクリックすれば削除できる

Refferences

• Excelのハイパーリンクから実際のアドレスを抽出するにはどうすればよいですか？

  • マクロ版

• Webページ上にある複数のリンクをコピーしてURLを一気にExcelへ取り込むワザ - 残業を減らす！Officeテクニック - 窓の杜

  • 関数版