社内共有・勉強用に作っていたのを上げてみました。
正規表現(regular expression)とは？
正規表現とは、文字列内で文字の組み合わせを照合するために用いられるパターンです。
MDN web docs より
一言でいうと、テキストから必要な箇所を抜き出すための手法です。
上記で英語を記載したのは、スプレッドシートで使える関数の頭に
regular expressionの略が記載されているからです。
| 関数 | 意味 |
|---|---|
| REGEXMATCH | 正規表現に一致するかをTrue, Flaseで返す |
| REGEXEXTRACT | 正規表現に一致する部分を抜き出す |
| REGEXREPLACE | 正規表現に一致する部分を置換する |
よく使う正規表現
| 文字 | 説明 | 正規表現の例 | マッチする例 |
|---|---|---|---|
| . | 任意の１文字 にマッチします。 | . | Ａ あ |
| + | 直前の文字が １回以上 繰り返す場合にマッチします。 最長一致。条件に合う最長の部分に一致します。 |
go+gle | gogle go...gle |
| * | 直前の文字が ０回以上 繰り返す場合にマッチします。 最長一致。条件に合う最長の部分に一致します。 |
go*gle | ggle go...gle |
| | | いずれかの条件 (OR条件) として使われます。 | goog(le | ol) |
| \ | 直後の正規表現記号を エスケープ します。 | go+gle | go+gle |
| [...] | 角括弧に含まれるいずれか１文字にマッチします。 | [abc] [a-c] |
a, b, c |
| [^...] | 角括弧に含まれる文字以外にマッチします。 | [^abc] [^a-c] |
a, b, c 以外の文字 |
| (...) | 文字を１つのグループにまとめることができます。 | goog(le | ol) |
| {n} | 直前の文字の桁数を指定できます。 | a{3} | aaa |
| {n,} | 直前の文字の最小桁数のみ指定できます。 | a{3,} | aaa aaaa... |
| {n,m} | 直前の文字の最小桁数と最大桁数を指定できます。 最長一致。条件に合う最長の部分に一致します。 |
a{3,4} | aaa aaaa |
| ^ | 直後の文字が行の 先頭 にある場合にマッチします。 | google... | |
| $ | 直前の文字が行の 末尾 にある場合にマッチします。 | google$ |
よく使う定義済みの正規表現
| 文字 | 説明 | 対応する表現 |
|---|---|---|
| \n | 改行 | (なし) |
| \d | すべての数字 | [0-9] |
| \D | すべての数字以外の文字 | [^0-9] |
| \s | 垂直タブ以外のすべての空白文字 | [ \t\f\r\n] |
| \S | すべての非空白文字 | [^ \t\f\r\n] |
※バックスラッシュ(\)は環境依存文字で円マーク￥(これの半角)で示されるケースもあります。
REGEX関連関数の詳細
| 関数 | 意味 |
|---|---|
| =REGEXMATCH(テキスト, 正規表現) | 正規表現に一致するかをTrue, Flaseで返す |
| =REGEXEXTRACT(テキスト, 正規表現) | 正規表現に一致する部分を抜き出す |
| =REGEXREPLACE(テキスト, 正規表現, 変換するテキスト) | 正規表現に一致する部分を置換する |
電話番号を抽出するときの考え方
基本的な考え方(電話番号)
例えば、テキストと混じっている 電話番号：01-2345-6789 から 01-2345-6789 を抽出するケースを考えてみます。
今回は一致する部分を抜き出したいので、 REGEXEXTRACT を使用します。
本来はテキスト部分はスプレッドシートでは
=REGEXEXTRACT(A1, 正規表現)
のようにセル指定すると思いますが、今回は
=REGEXEXTRACT("電話番号：01-2345-6789", 正規表現)
と文字列を直接入力する形で行います。
| 考え方の手順 | 関数 | 結果 | 行っていること |
|---|---|---|---|
| 1 | =REGEXEXTRACT("電話番号：01-2345-6789","\d") | 0 | 数字を抜き出したいので、数字を指定できる\dを入力 |
| 2 | =REGEXEXTRACT("電話番号：01-2345-6789","\d{2,3}") | 01 | 冒頭の数字は2,3回連続で続くので{2,3}で指定 |
| 3 | =REGEXEXTRACT("電話番号：01-2345-6789","\d{2,3}-") | 01- | 数字の後ろに来るハイフン(-)を指定 |
| 4 | =REGEXEXTRACT("電話番号：01-2345-6789","\d{2,3}-\d{4}") | 01-2345 | 再度数字が4回連続でくる指定 |
| 5 | =REGEXEXTRACT("電話番号：01-2345-6789","\d{2,3}-\d{4}-\d{4}") | 01-2345-6789 | ハイフン(-)と数字4回連続くることを指定 |
上記では03-3342-3612部分が4回と指定していますが、
この数字がどれだけ続くかわかっていない場合は
=REGEXEXTRACT("電話番号：01-2345-6789","\d+-\d+-\d+")
のように \d+ と表現しても今回は同じ結果が得られます。
ただ、連続する数値の回数がわかっている場合は指定した方が間違いなく得られる可能性は高いので指定してください。
応用編(電話番号)
電話番号を抽出するときに表記揺れに対応できるようにしてきましょう。
想定できる可能性としては下記があると思います。
※他あれば教えてください。
電話番号：01(2345)6789
電話番号：0123456789
先ほどと同じように 電話番号：01(2345)6789 のケースで関数を作っていくと下記の手順になります。
※()が入らないケースも考慮しています。
| 考え方の手順 | 関数 | 結果 | 行っていること |
|---|---|---|---|
| 1 | =REGEXEXTRACT("電話番号：01(2345)6789","\d") | 0 | 数字を抜き出したいので、数字を指定できる\dを入力 |
| 2 | =REGEXEXTRACT("電話番号：01(2345)6789","\d{2,3}") | 01 | 冒頭の数字は2,3回連続で続くので{2,3}で指定 |
| 3 | =REGEXEXTRACT("電話番号：01(2345)6789","\d{2,3}[-(]{0,1}") | 01( | ハイフンか(が0～1回連続すると指定 |
| 4 | =REGEXEXTRACT("電話番号：01(2345)6789","\d{2,3}[-(]{0,1}\d{4}") | 01(2345 | 再度数字が4回連続でくる指定 |
| 5 | =REGEXEXTRACT("電話番号：01(2345)6789","\d{2,3}[-(]{0,1}\d{4}[-)]{0,1}\d{4}") | 01(2345)6789 | ハイフンか)が0～1回連続すると指定+数字4回 |
[...] で示すことによってその中の何かが一致するという条件で抽出することが可能になります。
次の都道府県のケースをやるともっとわかりやすいかもしれないです。
住所から都道府県を抽出するときの考え方
都道府県のみを抽出する場合
住所：東京都新宿区*** ***タワー から 東京都 を抽出するケースです。
東京都以外にも対応できるように作成していきます。
| 考え方の手順 | 関数 | 結果 | 行っていること |
|---|---|---|---|
| 1 | =REGEXEXTRACT("住所：東京都新宿区*** ***タワー","[都道府県]") | 都 | 都道府県のどれかの文字に一致する文字を抽出 |
| 2 | =REGEXEXTRACT("住所：東京都新宿区*** ***タワー","\D{2,3}[都道府県]") | ：東京都 | 都道府県の前は2～3文字のテキストになるので指定してみる |
| 3 | =REGEXEXTRACT("住所：東京都新宿区*** ***タワー","[^：:\d]{2,3}[都道府県]") | 東京都 | うまくいかなかったので含めたくない文字を指定 必須ではないですが、今回は数値と全角半角のコロンを指定 |
[...] で 角括弧内の任意の位置文字に一致するという表現ができますが、角括弧の頭に ハット(^) を付けると否定の意味で特定の文字が入っていないという表現に変わります。
なので [^：:\d]{2,3} だと全半角のコロンと数字以外で2,3文字を指定するという表現になります。
空白まで抽出する場合
住所：東京都新宿区*** ***タワー から 東京都新宿区*** まで抽出するケースです。
上記に加えて空白以外でない文字までを選択すればよいので、[^ ] を加えれば抽出可能です。
もしくは、空白でない文字は \S で指定することが可能なのでそれを使うことも可能です。
=REGEXEXTRACT("住所：東京都新宿区*** ***タワー","[^：:\d]{2,3}[都道府県][^ ]+")
=REGEXEXTRACT("住所：東京都新宿区*** ***タワー","[^：:\d]{2,3}[都道府県]\S+")
注意点は \S の中に全角スペースは含まれていないので、全角スペース含めて対応でき別途全角スペースを含まないという設定をする必要があります。
=REGEXEXTRACT("住所：東京都新宿区*** ***タワー","[^：:\d]{2,3}[都道府県][^ \s]+")
会社名の表記揺れをなくしたい場合
このケースでは REGEXEXTRACT を使うより REGEXREPLACE を使用した方が考えやすいかなと思っています。
表記揺れが発生する可能性のある
株式会社, (株), （株）, ㈱, などを削除して一致する企業があるか確認します。(有限会社、合同会社も同様)
それらを全て対応させるために or 条件を指定できる | を使用していきます。
=REGEXREPLACE(”株式会社AAA”,"株式会社|\(株\)|（株）|㈱",""),"")
REGEXEXTRACTでやりたい方は下記でも可能です。
=REGEXEXTRACT(”株式会社AAA”,,"[^株式会社|\(株\)|（株）|㈱]+")
上記で半角括弧() の前にバックスラッシュが入っているのはエスケープといって、
特殊文字として認識される記号に対してこれは特殊文字ではなくて記号として扱ってくださいと指定するのに必要になります。
ここでいう特殊文字はこの記事の冒頭であげている表にある下記を指します。
\*+.?{}()[]^$-|
こんな感じで指定していっています。