Googleスプレッドシート/Excel

Googleスプレッドシートのハイパーリンクからリンク先をテキスト抽出する方法(Excel活用)

投稿日:2019年8月9日 更新日:

今回は、Googleスプレッドシートのハイパーリンクからリンク先をテキスト抽出する方法(Excel活用)をご紹介します。

公開されているスプレッドシート、「出版関連SNS Twitterアカウント一覧 (書籍系)」を活用させていただいたときに、

「ハイパーリンクからURLを抽出した複数を一括で開きたい」

と思い、どうすればいいのか調べたので共有します。

今回したいことのリスト

  1. GoogleスプレッドシートのハイパーリンクのリンクURLをテキスト抽出
  2. 上記URL群を一括でChromeブラウザで開く

です。

GoogleスプレッドシートのハイパーリンクのリンクURLをテキスト抽出

完成系は下記です。列項目「アカウント」のハイパーリンクを、赤枠のように、列項目「URL」に追記します。

元データは下記です。列項目「URL」がない状態です。

結論 スプレッドシートではすぐには抽出できないのでExcelで抽出する

Googleスプレッドシートで簡単にすぐに抽出できる方法が見つからず、Excelでなら、できる方法がありました。

手順は、

  1. Excelで元データを開く
  2. ハイパーリンクを抽出する
  3. 抽出URL群のデータをGoogleスプレッドシートにコピペする

です。

Excelで元データを開く

Excelで開きます。

ハイパーリンクを抽出する

続いて、VBEで抽出用の関数を作成します。

上部リボンの開発タブをクリックします。

開発タブがない場合は、設定する必要があります。

リボンの一番左の「ファイル」タブを開くと下記のように表示されます。

左メニュー欄の「オプション」を開きます。

Excelのオプションのモーダルが立ち上がりますので、左メニュー「リボンのユーザー設定」をクリックし、右側のリボンのユーザー設定の項目群から開発のチェックボックスにチェックをいれ、OKを押します。

開発タブが表示されるようになったと思います。開発内のVBEを活用するので、マクロの有効化をする必要があります。

具体的には、元データから別名保存(名前をつけて保存)し、下記画像のように、ファイルの拡張子を「Excelマクロ有効ブック(.xlsm)」に変更します。

下準備ができました。

開発タブをクリックすると、下記のようなモーダルが立ち上がります。

画像のように、挿入タブー標準モジュールをクリックします。

挿入-標準モーダルとクリックし、エディタ画面が表示されていると思います。

エディタ内に下記ようにコードをコピペします。

コード

Function GetURL(lnk As Range) As String
Dim Add As String
If lnk.Hyperlinks.Count > 0 Then
With lnk.Hyperlinks(1)
If .Address Like "http*" Then
Add = .Address
End If
End With
End If
If Add <> "" Then
GetURL = Add
Else
GetURL = ""
End If
End Function

コピペをしたら、上書き保存します。

上書き保存したら、モーダルを閉じExcelデータに戻ります。

上記のコードは指定したセルのハイパーリンクからURLをテキスト抽出する関数の作成コードです。

Excelで作成した関数を使用してハイパーリンクからURLをテキスト抽出します。

下記、画像のように、セル内で=getと入力すると、候補として、GetURL関数が表示されています。

=GetURL(セル指定)、という関数なので、ハイパーリンクの列項目「アカウント」のC4を指定します。=GetURL(C4)。

ハイパーリンクのURLがテキスト抽出されました。初回の関数実行したセルの右端をダブルクリックしてリストすべてのURLを抽出します。

抽出URL群のデータをGoogleスプレッドシートにコピペする

上記画像、赤枠のURLテキストをGoogleスプレッドシートのデータにコピペして追記してあげれば作業は終了です。

ハイパーリンクからURLを抽出した複数を一括で開きたい

ちなみに、URLをChromeブラウザで一括で開く方法は、拡張機能の「Pasty」が便利です。

chrome ウェブストアでインストールできます。

Chromeブラウザの右上から、「その他のツール」「拡張機能」をクリックします。

拡張機能の左下、chrome ウェブストアを開きます。

「Pasty」を検索して、インストールします。

今回なら、抽出したURLのセル群を一括コピーして、Chromeの右上、「Pasty」をワンクリックすると、複数タブとして一括でコピーしたURLを開くことができます。

ご相談・お仕事の依頼について

WEB制作・WEBマーケティングについてなど、気になる点があればお気軽にお問い合わせください。

-Googleスプレッドシート/Excel

執筆者:

関連記事

イフトのツイートの情報収集が自動する方法0

IFTTT(イフト)でツイート検索をスプレッドシートに自動保存する方法【30秒実装】

IFTTT(イフト)というツールを使えば、例えば、ルーティンでしているツイートの情報収集が自動でできます。 https://ifttt.com/ ツイートの情報収集が自動する方法 https://if …

イベント

イベント決済サイトを複数運用した際の運営を効率化する方法

クライアントのサイトでイベントチケットの導入しましたので、 各サイトの比較やその後の使用感など共有します。 イベント決済サービスのサイトの使用感比較 イベント決済サービスのサイトは主に5つほど。 この …

Excel(エクセル)パスワード不明のセル保護されているファイルの保護解除の対応手順

【1分】Excel(エクセル)のセル保護を解除する簡単な方法

今回は、Excelのセルが保護されていてマニュアル通りに解除してみても、パスワードが分からず解除できないときに解除させる方法をご紹介します。 正確には解除させるというより、ファイル自体を変換させること …

;