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担当者として月額サポートします。

Web365は貴社のWeb担当者として、制作改善運用をワンストップ定額サポートしています

・成果にこだわるサイト制作改善をお望みの方
・良いweb制作会社、コンサル会社がいないとお困りの方
・分析から改善運用まで一括で依頼したい方


是非Web365までご相談ください。






Web制作+Webマーケティングの個別講座開講中

Web365はWeb担当者のスキルセットである
サイトを変更する「提案力」と「技術力」について個別指導しております


・Webで集客するための「考え方」を知りたい方
・サイトの制作改善のセオリーを知りたい方
・自身のサイトでケーススタディをしたい方
・サイトの制作改善の「技術的なスキル」を習得したい方
・分析から改善運用まで自身でしたい方


どうぞWeb365の個別講座をご覧ください。

Googleスプレッドシート/Excel

執筆者:

関連記事

イベント

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

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

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

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

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

【コピペ】Googleスプレッドシートでアポ調整シートをつくりました

        Googleスプレッドシートでアポ調整シートをつくりました。 アポ前のご自身の候補日を先方にメールする際に、日程候補をコピペできるシートです。 …

CF7 Google Sheets Connectorが転記できないときの対応方法

CF7 Google Sheets Connectorが転記できないときの対応方法

CF7 Google Sheets Connectorが転記できないときの対応方法です。 クライアントサイトで使用してるのですが、2020年2月で転記してくれなくなったので、対応した方法をまを解説しま …

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

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

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

出欠確認ならGoogleフォームがおすすめと作成の流れ

Web担当のクライアント業務で、メールでイベント通知し、出欠をどう取るかという相談に対し、Googleフォームを作成しそのURLをメールに添えておくことが良いと回答しました。なるべく情報処理を減らして …

【簡単】Googleスプレッドシートでの単純作業はマクロ活用がおすすめと作り方

  Googleスプレッドシートでの単純作業が発生している際は、マクロ活用がおすすめです。 「マクロ、難しそう」と思っている方がいるかと思いますが、思っている以上に簡単ですので、一度お試しください。 …

^
;