今回は、Googleスプレッドシートのハイパーリンクからリンク先をテキスト抽出する方法（Excel活用）をご紹介します。
Web365とは
Web365は、外部Web担当者として、クライアントのWebサイトの集客力を高める施策を運用代行しています。
今回は、クライアントサイトを制作改善する際に行ったEXCELデータの処理の考え方の一部紹介です。
Web365について詳しく知りたい方は「Web365とは」ご覧ください。
公開されているスプレッドシート、「出版関連SNS Twitterアカウント一覧 （書籍系）」を活用させていただいたときに、
「ハイパーリンクからURLを抽出した複数を一括で開きたい」
と思い、どうすればいいのか調べたので共有します。
この記事でわかること
今回したいことのリスト
- GoogleスプレッドシートのハイパーリンクのリンクURLをテキスト抽出
- 上記URL群を一括でChromeブラウザで開く
です。
GoogleスプレッドシートのハイパーリンクのリンクURLをテキスト抽出
完成系は下記です。列項目「アカウント」のハイパーリンクを、赤枠のように、列項目「URL」に追記します。
元データは下記です。列項目「URL」がない状態です。
結論 スプレッドシートではすぐには抽出できないのでExcelで抽出する
Googleスプレッドシートで簡単にすぐに抽出できる方法が見つからず、Excelでなら、できる方法がありました。
手順は、
- Excelで元データを開く
- ハイパーリンクを抽出する
- 抽出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マーケティングの個別講座開講中
Web365はWeb担当者のスキルセットである
サイトを変更する「提案力」と「技術力」について個別指導しております
・Webで集客するための「考え方」を知りたい方
・サイトの制作改善のセオリーを知りたい方
・自身のサイトでケーススタディをしたい方
・サイトの制作改善の「技術的なスキル」を習得したい方
・分析から改善運用まで自身でしたい方
・Googleアナリティクスの設定、分析から改善施策の立案、レポート作成まで自身でしたい方
どうぞWeb365の「Webサイト制作、Webマーケティングの知識スキル習得するためのオーダーメイド個別講座」をご覧ください。
外注のWeb担当者として「制作」と「マーケティング」で単発/継続でサポートします。
Web365は貴社のWeb担当者として、制作改善運用をワンストップサポートしています
・成果にこだわるサイト制作改善をお望みの方
・自社にとって最適なweb制作会社、コンサル会社がいないとお困りの方
・分析から改善運用まで一括で依頼したい方
是非Web365までご相談ください。
【パートナー募集】
Web制作、Webコンサル、広告代理店、フリーランスの方々へのWeb改善プランの提供
Web365は同業他社/フリーランスの方々にも当方のWeb改善プランを提供し、クライアントサイトの改善プランのサポートや社内担当者へのレクチャーをサポートしています
・クライアントへのWebマーケティング、Web改善のプラン検討している方
・社内のWebマーケティングのリテラシーを高めたい方
・分析から改善運用まで一括でサポート依頼したい方
Web365は同業他社/フリーランスの方々にも当方のWeb改善プランを提供し、クライアントサイトの改善プランのサポートや社内担当者へのレクチャーをサポートしています
・クライアントへのWebマーケティング提案がしたいプラン作成をサポート
・クライアントへのWeb改善プラン実行のディレクションサポート
・社内のGoogleアナリティクスなどのレクチャー
・クライアントへのサイト分析から改善運用企画のアドバイザリーサポート
・Web系フリーランスへのWeb改善プランの提案企画書の作成サポート
・クライアントへのWeb広告運用代行
・クライアントへの計測ツール設定代行（Googleアナリティクス、eコマース、ヒートマップClarityなど）
まずは、お気軽にMTGでれきばと思いますので、Web365までご相談ください。
