Googleスプレッドシートにインポート等で取り込んだデータから必要な箇所だけを使いたい場合が有ると思います。
「不動産に関する様々なデータから、家賃、駅からの距離、築年数 だけを抜き出したい」
とか
「気象データから、温度、湿度だけを抜き出したい」
など、貴重なデータや、価値ある論文ではあるけれど、必要なのは一部分だけ、そんなことありますよね。
Googleスプレッドシートでも数式を使えばデータや論文等の長文から、簡単に文字列を抜き出す事が可能です。
数式を使って文字列を抜き出す方法は様々ありますが、中でも、関数SPLITを使い分割する方法を使われている方が多いと思います。
関数SPLITで参照データ（テキスト）と抜き出したい文字列の前の文字を指定し2つに分け、再びSPLITで、今度は文字列の後ろの文字を指定する…
そんな感じではないでしょうか？。もちろんこれでも必要箇所を抜き出す目的は果たせるので問題はないのですが、少し二度手間に感じますよね。
できればもっと効率的に、見た目的にも分かりやすい処理にしたい。
そのような場合は、数字は「\d」、英数字は「\w」、文字が一回又は複数回繰り返す場合は「+」など、文字列の集合を一つの文字列で表現する、正規表現を利用する方法をオススメします。
そこで今記事では、Googleスプレッドシートの数式で正規表現を使い、データや長文から必要箇所を一発で抜き出す方法を紹介します。
文字列を抜き出すだけなのに、数式が入れ子で分かりにくくなってしまう、そんな状態も緩和できるので是非参考にしてみてください。
 |
|
 |
Googleスプレッドシートで関数SPLITを使い長文から文字列を抜き出す
正規表現を使う方法を紹介する前に、まず関数SPLITを使い文章を分割する方法で目的箇所の文字列を抜き出したいと思います。
そして後に紹介する、正規表現を使った方法と、どちらがシンプルで見やすく使いやすいか比べてみてください。
では、オンラインプログラミングスクール「TechAcademy [テックアカデミー] 
」のHPのURLを関数IMPORTXMLに指定して取得したデータを使って、任意の文字列を抜き出してみましょう。
関数IMPORTXMLにURLとXPathを指定してデータ（テキスト）を取得しました。では、セルB1に入力されたデータ（テキスト）から「最大30万円」の「30」を抜き出したいと思います。
 |
|
まず「30」の前の文字「”大”」を関数SPLITの分割文字に指定して、データを分割します。
=SPLIT(B1,"大")
指定した文字「”大”」を堺にして2つのセルに分割できました。
関数INDEXを使用して、分割したデータ（テキスト）の、抜き出したい文字列が含まれる一方を指定します。
前後に分割したデータ（テキスト）の後の方に「30」があるので、INDEXの引数に「2」を指定します。
=INDEX(SPLIT(B1,"大"),2)
「30」が含まれる後方のデータ（テキスト）を取得できました。
今度は、「30」の後ろの文字「”万”」を、関数SPLITの分割文字に指定。関数INDEXの引数には1を指定します。
=INDEX(SPLIT(INDEX(SPLIT(B1,"大"),2),"万"),1)
該当文字列の前後の文字で2回分割する事により「最大30万円」の「30」を抜き出すことができました。
抜き出す文字列を挟んでいる前後の文字を分割文字として関数SPLITに指定し前後に分割、それを関数INDEXも合わせて同じ処理を2回行い、目的の文字列を取得。
少し煩雑に感じませんか？
正規表現を使って長文から文字列を抜き出す
今度は正規表現を使って「テックアカデミー」のHPから取得した同じデータ（テキスト）を使い「販売価格の値」を取得してみましょう。
Googleスプレッドシートで正規表現を指定し、目的の文字列を取得するには
関数REGEXEXTRACT
を使用します。
使い方としては、関数REGEXEXTRACT の第1引数に参照するデータ（テキスト）を、第2引数に取得する文字列に合致する正規表現を指定。
「テックアカデミー」のHPデータ（テキスト）を第1引数にして、関数REGEXEXTRACTに数字を表す正規表現「\d」を指定すると。
=REGEXEXTRACT(B1, "\d+")
指定した正規表現に一致する最初の文字列をかえすので、これだけで、「30」が抜き出せました。
特定の文字に挟まれた数字を抜き出す
今度は、参照するデータ（テキスト）の後半の位置にある、「サポート1000社以上」の「1000」を抜き出してみましょう。
数字を表す正規表現「\d」又は「[0-9]」と1回又は複数回の繰り返しを表す「+」を組み合わせ、「ト」と「社」で囲みます。
=REGEXEXTRACT(B1 "ト(\d+)社")又は
=REGEXEXTRACT(B1, "ト([0-9]+)社")
参照するデータ（テキスト）に含まれる、「ト」と「社」に挟まれた箇所の「数字」が抜き出せました。
桁区切りのカンマ「,」が含まれた数字を抜き出す場合
正規表現で「\d」又は「[0-9]」を指定すれば数字を一致させる事はできますが、注意点としてこれだけでは「10,000」の様な桁区切りのカンマ「,」が含まれた数字は一致させる事はできません。
例えば、価格情報のデータ（テキスト）等の多くは、カンマ付き数字表示なので、「\d」又は「[0-9]」だけの指定で対応は難しいと思います。
試しに旅行サイト「日本旅行 」のHPのHTMLをGoogleスプレッドシートのセルにコピー＆ペーストし、任意の文字列を抜き出してみましょう。
Googleスプレッドシートのセルにコピー＆ペーストした「日本旅行」のHPに掲載されているプラン箇所のHTML。
このHTMLに含まれる価格の値はカンマ付き数字表示になっているので、正規表現「\d」又は「[0-9]で一致しない為、価格の値を正しく抜き出すことはできません。
では、正規表現を使って、総額の値「40,400」を抜き出してみたいと思います。
「40,400」この値を正規表現的に要約すると
「1桁から3桁までの数字」 + 「カンマ（,）」 + 「三桁の数字」
と分解できるので「\d{1,3},\d{3}」と表せます。
そして、前後のタグを含めた文字列で「\d{1,3},\d{3}」を挟めば、該当する箇所と一致する正規表現になるはず。
=REGEXEXTRACT(A1, "総額：</span><b>(\d{1,3},\d{3})</b><span>円")
カンマ付き数字で表示される総額の値、「40,400」を抜き出すことができました。
特定の文字に挟まれた英単語を抜き出す
抜き出す文字列が英単語（アルファベット）の場合、それに対応する正規表現を指定する必要があります。
・抜き出す文字列が アルファベット の場合は [A-Za-z]
これでアルファベットの大文字・小文字の両方に対応できます。
BTOパソコンメーカー「【FRONTIER】
」のHPのデータを関数IMPORTHTMLで取得し、任意の文字列を抜き出してみましょう。
関数IMPORTHTMLにURLを指定してデータ（テキスト）を取得しました。
では、取得したデータ（テキスト）から大文字と小文字が混ざった「GeForce」を抜き出してみましょう。
|
|
取得したHTMLから、文字列「GeForce」を抜き出す場合は、前後の文字「”A “」と「” R”」と、それに挟まれた英単語（アルファベット）表す正規表現[A-Za-z]を指定します。
=REGEXEXTRACT(B1, "A ([A-Za-z]+) R")
前後の文字「”A “」と「” R”」に挟まれた正規表現[A-Za-z]に一致する箇所の文字列「GeForce」が抜き出せました。
特定の文字に挟まれた日本語を抜き出す
抜き出す文字列が日本語の場合もそれに対応する正規表現を指定する必要があります。
・抜き出す文字列が ひらがな の場合は [ぁ-ん]
・抜き出す文字列が カタカナ の場合は [ァ-ン]
・抜き出す文字列が 漢字 の場合は [一-龥]
これらが全て含んでいる文字列の場合は [一-龥ぁ-んァ-ン] で大体対応できると思います。
「テックアカデミー」のHPから取得したテキストから、文字列「プログラミングを学べる」を抜き出す場合は、前後の文字「る」と「専」、それに挟まれた日本語[一-龥ぁ-んァ-ン]を表す正規表現を指定します。
=REGEXEXTRACT(B1, "\る([一-龥ぁ-んァ-ン]+)\専")
指定した正規表現と一致する箇所の文字列「プログラミングを学べる」を抜き出すことができました。
まとめ：正規表現を使って効率的に文字列を抜き出す
Googleスプレッドシートの数式で正規表現を使い、データや長文から必要箇所を一発で抜き出す方法を紹介しました。
Googleスプレッドシートで特定箇所の文字列を抜き出す方法として、関数SPLITに参照データと文字を指定し、分割する方法を利用する事で、文字列を抜き出す目的は果たせますが、文字列の前後で2回分割処理する必要があります。
しかし今回紹介した、正規表現を使った方法であれば、シンプルに一度の処理で目的箇所から文字列を抜き出す事が可能。
又、「数字」「アルファベット」「日本語」と、抜き出した結果の文字列を限定する事ができるので、
例えば、数字の入力を想定したセルに「アルファベット」や「日本語」など、数字以外の文字列が入力されてしまう様な誤動作を防止できます。
正規表現のルールを覚えて使いこなすのは大変ですが、今記事で紹介したシンプルな数式は定型文として使えるので、Googleスプレッドシートでデータや長文から文字列を抜き出す処理が必要な場面で是非使ってみてください。
 |
|
 |
|
 |
国内で10年前からサービスを提供しているタスク管理・プロジェクト管理ツール。
利用者が100万人を超え、エンジニアの方以外にも、事務やデザイナー、マーケター、営業など、多様な職種の方にお使いいただいています。 |
|
|
 |
|
 |
|
 |
|
 |
|
 |
|