REGEXEXTRACTでパターンを取り出す

REGEXMATCHは、パターンが存在するかどうかを教えてくれます。しかし、Order #4821から数字を取り出したり、メールアドレスからドメインを取り出したりするように、一致したテキストそのものが必要になることもよくあります。

その役割を担うのがREGEXEXTRACTです。パターンに一致するテキストの最初の部分を見つけ、その部分をセルの値として返します。

これもGoogle Sheetsの関数です。インポートした整理されていないデータから、構造化された部分を取り出す方法として、非常にわかりやすい関数の1つです。

REGEXEXTRACT(text, pattern)は、textの中でpatternに一致する最初の部分文字列を返します。

一致するものがない場合は、空白ではなく#N/Aエラーを返す点に注意してください。

次の数式は、A2から最初に連続している数字を取り出します。Order 4821の場合は4821を返します。

REGEXEXTRACTは、一致した文字が数字であっても、常にテキストとして返します。そのため、4821は合計などの計算に使える数値ではなく、テキスト文字列として返されます。

実際の数値に変換するには、VALUEで囲みます。次の数式は数字を抽出して変換するため、その結果を使って計算できます。

パターンは数字に限られません。大文字の接頭辞を取り出すには、文字クラスと量指定子を使います。

次の数式は、INV2024のようなコードの先頭にある英字を取り出し、INVを返します。[A-Z]+は、連続する1つ以上の大文字を意味します。

A2にSKU-4821-REDが入っていて、中央の数字だけを取り出したいとします。パターン内で前後のコンテキストを指定すると、位置を表現できます。

次の数式は、2つのハイフンの間にある数字を探し、4821を返します。リテラルのハイフンによって、正しい数字の並びを見つけられます。

最後の式にある括弧、(\d+) に注目してください。これはキャプチャグループです。

パターンにキャプチャグループが含まれている場合、REGEXEXTRACT は一致全体ではなく、括弧内にある部分だけを返します。そのため、-(\d+)- はハイフンを除いた 4821 を返します。

キャプチャグループを使うと、正確に一致させるために周囲の文脈もパターンに含めながら、必要な部分だけを抽出できます。

sam@coddykit.com から、ドメインである coddykit.com を取り出したいとします。@ に一致させてから、その後ろから末尾までをキャプチャします。

次の式は、A2 のアットマークに続く部分を返します。(.+) は任意の文字を1文字以上キャプチャし、$ によって文字列の末尾までに限定します。

アンカーを使うと、既知の位置から一部を取り出せます。最初の単語だけをキャプチャするには、先頭を指定し、最初のスペースで止めます。

次の式は、A2 の先頭にあるスペース以外の文字をキャプチャします。John Smith に対しては John を返します。[^ ]+ は、スペースではない文字が1文字以上続くことを意味します。

抽出に失敗すると #N/A エラーが発生するため、異なるデータが混在する列で式を下方向にコピーすると、シートがエラーだらけになることがあります。

呼び出しを IFERROR で囲むと、エラーの代わりに適切な値を返せます。次の式は、数字が見つかった場合はその数字を返し、見つからない場合は空文字列を返します。

パイプ | はまたはを意味します。1つのパターンで複数の候補に一致させることができます。コードが異なるプレフィックスで始まる可能性がある場合に便利です。

次の式は、A2 に含まれるステータスワードのうち、OPEN、CLOSED、HOLD のいずれかを抽出します。最初に見つかったものが返されます。

REGEXEXTRACT は、インポートしたデータの列全体に適用すると力を発揮します。各行から一致する部分を取り出し、並べ替えや分析ができる新しい列に整理できます。

たとえば、A列に Order 4821 - shipped のような注文文字列が入っているとします。次の式をB列に下方向へコピーすると、すべての行から注文番号だけを抽出できます。乱雑な元のテキストから、数値のように扱える整然とした列を作成できます。

一致した部分の抽出について、理解度を確認しましょう。

REGEXEXTRACT(text, pattern) は、Google Sheets で最初に一致した部分文字列を返すことを学びました。重要なポイントは次のとおりです。

• 結果は常にテキストです。数値として扱うには VALUE で囲みます
• 括弧内のキャプチャグループは、その中の部分だけを返します
• 一致しない場合は #N/A になるため、IFERROR で対処します
• | を使うと、1つのパターンで複数の候補に一致させられます

次は、テキストを読み取るだけでなく、REGEXREPLACE を使ってその場で書き換える方法を学びます。