Googleスプレッドシート/Excel

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

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

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

Web365とは

Web365は、外部Web担当者として、クライアントのWebサイトの集客力を高める施策を運用代行しています。

今回は、クライアントサイトを制作改善する際に行ったEXCELデータの処理の考え方の一部紹介です。

Web365について詳しく知りたい方は「Web365とは」ご覧ください。



公開されているスプレッドシート、「出版関連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マーケティングの個別講座開講中

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までご相談ください。

Googleスプレッドシート/Excel

執筆者:

関連記事

Googleカレンダーと連携した日程予約カレンダーをつくりました

日程予約カレンダーを作成しました。 アポ前の社内調整をGoogleカレンダーでカンタンにできます。 カレンダーの対応可能日程のみをフォームに表示させ、第3希望まで選択できるフォームです。 カレンダーは …

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

Googleフォームの概要と作成の流れを解説します。 Web365とは Web365は、外部Web担当者として、クライアントのWebサイトの集客力を高める施策を運用代行しています。 今回は、クライアン …

【学習方法】シニア向け、初心者の方がGmail、Zoom、You Tubeを学習する方法

質問にて、シニア向け、初心者の方が、100名規模のグループに対し、連絡、アンケート、オンライン会議等を実施するためのツールとして、どんな物が良いか?Gmail、Zoom、You Tubeをおすすめし、 …

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

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

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

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

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

Wordでチェックボックスを簡単につくる方法

クライアント先にて、チェックシートの作成で大量にチェックボックスをつくるので、簡単な方法はないかと相談があったので、作り方をいくつかまとめて解説します。 考え方 2回する作業はできるだけ、仕組み化して …

【簡単】Googleカレンダーの予定から月次業務実績表を作成する方法

Googleカレンダーの予定から月次の業務実績表を作成する方法、流れを解説します。 Web365とは Web365は、外部Web担当者として、クライアントのWebサイトの集客力を高める施策を運用代行し …