「Googleスプレッドシートから見た！」Excel 14の新関数 -11 TEXTBEFORE / TEXTAFTER つづき

Excelに追加された 14の新関数を Googleスプレッドシートからの視点で検証する記事も 11回目 。

Excel 14の新関数シリーズの 最終回です。

14の新関数 の最後の 2つ。 テキスト操作系の TEXTBEFORE、TEXTAFTER を前回に続いて検証していきます。

• 関数の特徴

• Excelでの メリット、デメリット、活用　← 前回ここまで

• Googleスプレッドシートの機能、関数との違い

• Googleスプレッドシートでは無い機能を どう補うか

この流れで検証きました。

シリーズ前回の記事

EXCEL 14の新関数 TEXTBEFORE / TEXTAFTER

• 区切り文字の前を取り出すのが　TEXTBEFORE関数

• 区切り文字の後を取り出すのが　TEXTAFTER関数

こちらの2つのExcel 新関数、Excel上での動きの基本や使い方、活用例は 前回たっぷり書いたので、今回は Googleスプレッドシート視点で 検証してきます。

Googleスプレッドシートの機能、関数との違い

TEXTBEFORE / TEXTAFTER に該当する 関数は 残念ながら Googleスプレッドシートにはありません。

しかし、Googleスプレッドシートには Excelにはない 文字列を取り出す最強の関数があります。

それが REGEXEXTRACT関数 です。

文字列操作系最強関数 REGEX3兄弟

ExcelとGoogleスプレッドシート、互いに関数の輸入・逆輸入を繰り返し、当初あった Googleスプレッドシートの関数における優位性はだいぶ薄れてきました。

Googleスプレッドシートの 最強関数四天王、FILTER、SORT（SORTBY）、UNIQUE、そして今回 SPLIT（TEXTSPLIT）が EXCELでも使えるようになり、EXCEL は 一行数式で出来ることが格段に広がりました。

でも、いまだにExcelには無い Googleスプレッドシート オンリーの 最強関数が 幾つかあります。

代表となるのは QUERY関数で、どちらかというとこの最強集計関数に目がいきがちですが、もう一つ（3つ） REGEX3兄弟という文字列操作系最強関数が存在します。

REGEX3兄弟とは、文字通り REGEXから始まる3つの関数で 正規表現を使って 超柔軟な文字列処理が実現できる Googleスプレッドシートにしかない関数です。

今回は TEXTBEFORE / TEXTAFTER の代替なので、文字列を抽出する REGEXEXTRACT の出番となります。

難点は 正規表現 が、ひじょーに初心者殺しで 可読性が低いという点。

Googleスプレッドシートでは無い機能を どう補うか

TEXTBEFORE / TEXTAFTER の代表的な特徴

• 指定した区切り文字の 前（後ろ）を取得できる

• 区切り文字は複数指定できる

• スピル対応（複数セルを処理できる）

• マイナスで 後ろから〇番目の区切り文字といった指定ができる

• 〇番目の区切り文字といった、自由な指定ができる

これらを全て満たすことを考えると、とんでもなく複雑で長い式になりそうなので、「やりたいこと」に合わせて 上で紹介した REGEXEXTRACT関数をベースに 式を作って対応するのが良さそうです。

ケースごとに検証していきましょう。

ケース1. 区切り文字（1つ）の前、もしくは 後ろを取得したい

いちばんシンプルな基本形ですね。

このような ・ 区切りの前（後ろ）を取り出したい場合、Googleスプレッドシートで対応するにはどうすればよいでしょうか？

幾つか方法がありますが、まずは 複数の関数を組み合わせず 一つの関数で対応できる REGEXEXTRACT関数を使ってみましょう。

正規表現の説明に突入すると、とても1回では終わらないので 解説はサクっといきます。

Googleスプレッドシートの関数における 正規表現は、残念ながら 正規表現の 先読みや後読み が使えない(NOT SUPPORTED) ってことで、カッコをつけて キャプチャグループをつくることで 必要な部分だけを取り出しています。

正規表現を使ったことない人にとっては、もうなんだかわからんですよね？　

専門用語はあまり気にしないでください。

一応、Googleスプレッドシートにおける 正規表現と 正規表現が使える関数は、まいどお馴染み 「いきなり答える備忘録」さんがまとめてくれています。

正規表現の 貪欲（最大マッチ）、怠惰（最小マッチ） をざっくり理解する

上の関数解説の「最小のマッチ」という部分は、

という意味なんですが、貪欲とか大食いとか怠惰って・・・。
七つの大罪か！って思いますよね。

簡単にいうと 今回のようなケースで 区切り文字 ・が、複数あった場合は、

とすると、一つ目の ・ の手前を返して、

の場合は 最後の ・ の手前を返すってことです。

↑ こんな感じ

今回は TEXTBEFORE の挙動と揃える為、1つ目の区切り文字にヒットする 最小マッチの記述にしています。

注意：区切り文字がメタ文字の場合はエスケープを

REGEXEXTRACT で処理する際の注意点として、区切り文字が 正規表現における メタ文字（例えば . や * 半角カッコなど）の時は、エスケープ処理が必要ってのがあります。

\ をつけることで メタ文字を 通常の文字として扱えます。

別解：SPLITで処理する場合

どうしても正規表現は 苦手・・・。っていう人は、SPLITを使う方法でもいいいでしょう。

前 は、SPLITして１つ目をINDEXでとるだけなんで簡単ですね。

一方、後ろは 区切り文字が複数登場するケースを考慮すると面倒です。ここで役立つのが レガシーな 置換関数 SUBSTITUTE です。

Googleスプレッドシートでの関数による置換処理は、正規表現の使える REGEX３兄弟の長兄（ラオウ的ポジション）、 REGEXREPLACE が圧倒的に便利すぎるんで、ついそっちを使っちゃいますが、まだまだEXCELでは SUBSTITUTE の出番も多いです。

SUBSTITUTE関数のメリットは、置換対象の文字 に対して 〇回目に 登場した ものだけ置換する、といった指定が出来る点。

SUBSTITUTE(A2,"・","_",1)

これで、対象の文字列の １つ目の ・ だけ、文字列に登場しない _ に置換してからSPLITで _ で区切ることで、文字列が ２つに区切られることになります。あとは 後ろ（２つ目）を INDEXで取り出せばOK。

複数の関数を組み合わせる必要がありますが、こちらの方がわかりやすいかもですね。

ケース2. 区切り文字の複数指定、スピル対応

ケース1で使用した REGEXEXTRACT関数 を使った指定区切り文字の前（後ろ）を取り出す方法は、区切り文字の複数指定や、複数セルの一括処理（スピル）にも使えます。

REGEX3兄弟は Arryaformulaと組み合わせて スピル対応できる ってのも最強の理由の一つです。

まず区切り文字の複数指定ですが、A列に適当に並べた ゲームタイトルのうち、よーく見ると

信長の野望･新生

だけ、・が全角ではなく半角の ･ というのがわかります。。まぎらわしい！

この・の全角、半角両方を区切り文字として指定する為に

[・･]

という書き方で指定しています。これは正規表現における 1文字の複数指定（または）を意味します。

1文字ではない 複数指定（または）の記述は | を使うのですが、これは TEXTSPLITの回で少し触れています。

画像の6行目のように空白セルを対象としたり、区切り文字が見つからない場合は、 #N/A エラーになるので、これは IFERRORあたりで エラー時の処理を追記した方がよいでしょう。

別解：スピル対応は出来るが区切り文字複数パターン対応が厳しい

ケース1と同じく、ケース2も 正規表現を使わない別解を書いておきましょう。

TEXTSPLITの回で触れましたが、GoogleスプレッドシートのSPLITは 区切り文字を連続で記載するだけで 複数区切り文字指定となります。（ただし 単文字に限る）

なので、前を取り出す場合は比較的簡単です。

ちなみにスピル対応の方ですが、INDEX関数自体が 配列を返せる関数である為、Arrayformulaを使う必要がありません。INDEXだけでスピらせることができるので 簡潔に記載が可能です。

ただし、これはあくまでも 1番目の区切り文字の「前」を取り出すときだけ使える式です。

複数区切り文字指定で後ろを取り出したり、〇番目の区切り文字の前を取り出したい場合は通用しません。

複数区切り文字に対しては

SUBSTITUTE(SUBSTITUTE(A2,"・","_",1),"･","_",1)

このように SUBSTITUTEを2重（入れ子）にして処理できそうにも思えますが、区切り文字の並びによっては 2つ _ に置換され その後のSPLITで _ で分割した際に  3つに分割されてしまう恐れがあります。

これだと ただしく　1つ目の ・または ･ で区切った 後ろ を取り出すことができませんし、 2番目の ・または ･ で区切った前を取り出す際も期待する結果とならないケースが出てきます。

複数区切り文字指定 のケースは 正規表現の使えるREGEXEXTRACTを使った方が無難と言えます。 

ケース3. 最後に登場する区切り文字の前（後ろ）を取得したい

EXCELの TEXTBEFORE / TEXTAFTER なら、第3引数に -1を 指定するケースです。

非常に完結に書けますし、もちろんスピル対応可能。

Googleスプレッドシートで代替する場合、これも REGEXEXTRACT でいけます。

あまり需要なさそうですが、最後の区切りの前を取り出す場合は、正規表現部分の最小マッチを最大マッチ（貪欲マッチ）に変えれば良いだけです。

つまり

"^(.*？)[・･]"

から ？ をとって

"^(.*)[・･]"

とするだけ。

一方需要がありそうな 最後の区切りの後ろを取り出す場合は、

".*[・･](.*?)$"

このように 前に .* の貪欲マッチを追記することで [・･](.*?)$ は 最小マッチとすることができます。

動きとしては文頭からサーチしていくため先に記述した .*の大食いが強く作用するので、(.*?)$"の ?を省略して

 ".*[・･](.*)$"

としても、結果としては同じになります。

なんか 絵文字（アスキーアート）が並んでるみたいｗ

別解：SPLIT + INDEX では 最後の区切り文字の後ろ取得ができない → LAMBDヘルパー関数で対応

最後の区切り文字の前 を取得は使うケースがあまりないので、後ろを取得だけ別解を入れておきます。

なんとなく SPLIT+ INDEX で分割した 後の最後（一番左）を取得すれば良さそうですが

Googleスプレッドシートには、 -1で後ろからという指定ができる関数が（現時点では）ないのと、SPLIT + INDEX だと 区切り文字の数がバラバラの セル範囲を対象とする場合、一括で 一番後ろの区切り文字の後ろを取得が出来ません。（一つの大きな長方形範囲とみなしてしまう為）

※本来はこの記事の公開時点 2023/02/25 では、Googleスプレッドシートの最新輸入関数 CHOOSECOLSが使える想定でしたが、一度使えたのに不具合があった為 停止されてしまいました。

仕方がないので、1セルずつ 分割 → 分割した要素数を取得 → 最後の要素を取得 を繰り返す LAMBDAヘルパー関数を使用します。

今回の場合は BYROW、 MAPでもどちらでもOK

ヘルパー関数内で 最新関数 LETを使って、SPLIT で分割した結果を arrと置いています。COLUMNS(arr)が分割後の配列の横の長さ、つまり 最大値なのでINDEXでそれを指定することで一番後ろが取得できます。

ケース4. 〇番目の区切り文字の 前（後ろ）を取得したい

実はこれが一番面倒（複雑）なので、最後にもってきました。

たとえば 3番目の ・･ の前（後ろ）を一括で取得したい場合

複雑さが増してきましたね。

後を取り出す式の方がわかりやすいので、そっちから解説しましょう。

今回 3番目の後ろを取り出したいので、文頭から 

このような 繰り返しの後ろを取り出したいってことになります。
これを正規表現で表すと

"^(.*?[・･]){3}"

となります。

※{3}は直前の 文字もしくはグループを 3回繰り返すという意味

ただ、これだとカッコ内が キャプチャグループ扱いで取り出す対象となってしまいます。

これをキャプチャしない グループ化する為の記述が

(?: ここに正規表現)

になります。

これを踏まえると 前を取り出す方は

こうなるのがわかりますね。前を取得する場合は、対象となる3番目の区切り文字を含めないようにする為、2回繰り返しとその後ろという 正規表現にしています。

〇番目の区切り文字の 前（後ろ） を取得する式を 汎用性あるものにする

この〇番目の区切り文字の 前（後ろ）を取得する式を 汎用性あるものにすれば、かなり TEXTBEFORE / TEXTAFTER の代替式といえるものに近づきますね。

LAMBDA の引数を TEXTBEFORE / TEXTAFTER の引数に揃えたんで 長くなってますが、汎用性のある式になったんじゃないでしょうか？

もちろんこのまま 名前付き関数 にもできます。

ただ、この式は以下に対応していません。

• 対象のテキストが 改行を含んでいるケース

• 区切り文字が 2文字以上の文字列のケース

• instance_num をマイナス指定で 後ろから〇番目としたいケース

これらも REGEXEXTRACT関数で処理できますが、ケースごとに個別に正規表現を作っていくしかないです。

もしかすると、これ以外のケースでもエラーや誤抽出になるかもしれません。

正規表現は、バグ（エラーが発生するケース）を見つけにくいってのも難点です。

※このケースが考慮できていない、この使い方だとエラーが出るといったものが見つかりましたらお知らせください。

Q. カッコ内の文字を取り出してみよう

最後に TEXTBEFORE / TEXTAFTERの応用問題を Googleスプレッドシートでチャレンジしてみましょう。

なお、この問題は エクセルの神髄さん Twitterお題を参考にさせていただきました。

A1に入った画像のような文章から カッコの中の文字だけ 横に展開するように1つの式で抽出したい。

※括弧は全角カッコとする。（←ここだけ少し簡単にしました）
※括弧が正しく対になっているものとする

Excelでの回答は  エクセルの神髄さんを参考に。

これをGoogleスプレッドシートで処理できそうでしょうか？

まずは考えてみてください。

↓↓↓
回答は以下
↓↓↓

A. カッコ内の文字を取り出してみよう

いくつか方法がありますが、まずは REGEXEXTRACT関数を使う方法からいってみましょう。

A1. REGEXEXTRACT関数で抽出する

SPLIT、FILTER、REGEX3兄弟の三男 REGEXMATCH、さらに最新輸入関数のLETまで使ってますｗ

文字列抽出の最強関数 REGEXEXTRACT ですが、正規表現にマッチした 最初の 文字列のみ を抽出する（仕様となっており、マッチする複数の文字列を全て抽出したい場合は 使いにくい、という弱点があります。

そこで

こんな処理手順で カッコ内の文字を抽出しています。

A2. REGEXREPLACE関数で カッコ内の文字だけを残す

正規表現は複雑ですが、REGEXREPLACE と SPLIT の2つの関数で処理する方法です。

REGEXEXTRACT関数では難しい複数のマッチする文字列を抽出したい場合、最初に分割する方法 回答1で使った方法以外に、REGEXREPLACE関数を使う方法があります。

要は取り出したい文字列以外の邪魔な部分を、区切り文字に置き換えちゃうという方法です。

キャプチャグループで取り出したものは $1 という記述で利用することができます。これを活用しています。（キャプチャグループが複数ある場合は $2、$3と していきます）

最後に SPLITで分割すれば OK。

A3. 正規表現なし SPLIT + Query で取り出す

正規表現を使わない方法も紹介しておきましょう。

これも手順が多いです

こんな処理をしています。参考までに。

Excelの応用問題を、TEXTBEFORE / TEXTAFTER のない Googleスプレッドシートでチャレンジしてみました。

TEXTBEFORE / TEXTAFTER なくてもいいけど、あればやっぱり便利だなと思いますね。

EXCEL 14の新関数 シリーズ終了です

全11回、3か月くらいこのシリーズやってきましたが、ようやく今回で 14の新関数シリーズも終了です。長かったー。

久しぶりに Excel（オンラインの方）を結構触ったんで、色々発見もあって自分も楽しめました。

Excel14の新関数シリーズは、Googleスプレッドシートには無い Excelの新関数を Googleスプレドシート視点で検証する ってテーマだったんですが、2月月初旬に、なんと14の新関数の 配列操作系関数を中心に Googleスプレッドシート にも新関数が追加（輸入）されちゃいましたｗ

LAMBDAのタイミングで輸入されなくて、もう輸入ないかと思われた LETも登場。

じゃあ、次はこれを取り上げよう！ って思ってたんですが・・・。
先週くらいから 配列操作系の関数が停止中なんですよね。。

というわけで、来週の休日更新 noteは 状況次第で内容考えます。