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

14の新関数のうち 11の配列操作系が前回で終わり、残るは テキスト操作系の3つ。

今回は、その中でも基本となる TEXTSPLIT を取り上げます。

• 関数の特徴

• Excelでの メリット、デメリット、活用

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

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

主にこの 4つの視点で検証していきます。

シリーズ前回の記事

先週は 流行りの ChatGPTの記事入れちゃったんで一週あいちゃいました。でも、やっぱタイムリーな記事は 閲覧数も スキ数も違うわーｗ

EXCEL 14の新関数 TEXTSPLIT

テキスト操作系新関数のTEXTSPLIT 、これは Googleスプレッドシートの SPLIT関数の Excel版と言える関数です。

しかし Excel 14の新関数シリーズの初回にも書いたけど、Excelには テキスト分割できる関数はなかったのね。

TEXTJOINは結構前からあったし、スピル対応して色々 Googleスプレッドシートから 便利な関数を逆輸入してたのに、なぜかSPLITは後回しだったみたい。

でも、あえて名前を変えて遅れて登場しただけあって、SPLIT関数に無かった 便利機能が色々追加されています。単体でも超使える便利な関数です。

Excelでの動きを検証していきましょう。

TEXTSPLIT の特徴・基本理解

上で書いた通り、Googleスプレッドシートの SPLIT関数の機能増強版です。

セル内の文字列 を 指定した区切り文字で 分割し、複数セルに展開（スピル）する関数です。

6つも引数がありますね。

ちなみに Googleスプレッドシートの SPLIT関数は 以下のような 4つの引数になっています。

引数だけ見ても 違いそうな感じがしますよね。

今回の TEXTSPLITは Googleスプレッドシート視点というよりも、スプレッドシートのSPLIT関数と比較しながら 見ていくのが分かりやすそうです。

特徴、メリット、デメリット に触れながら、SPLIT関数との比較を合わせて検証していきましょう。

第1引数 text ・・・分割するテキスト。

第1引数は 対象とする テキストです。通常はセル参照とすることが多いですが、もちろん 直接 "i love pdf" といった文字列を入れても良いですし、この部分を数式で生成するケースもあります。

注意点として 空白セル、または 空文字が入ったセルを指定した場合はエラーとなります。

※ 第2引数以降で設定した 区切り文字が 一つもない 文字列の場合は、そのまま 第1引数が返ります。

この第1引数の配列（スピル）対応については、次週に回します。

第2引数 col_delimiter ・・・　横（列方向）の区切り文字

第2引数の col_delimiter は 横（列方向）の区切り文字の指定です。
これはGoogleスプレッドシートと基本的には同じですが、少し使い方が違う部分もあります。

第2引数は重要なので、じっくり検証してみましょう。

①通常（1つの単文字）の横区切り

B2セル内の文字列を ,（カンマ）で区切って 横方向に展開しています。もちろん複数セルにスピります。画像は 式を入れたC2セルからF2セルまでスピってます。

②1つの 文字列での横区切り

区切り文字は 単体文字である必要はなく 2文字以上で構成される 文字列も指定できます。②の例は　" and " （前後に半角スペースのある and ）で区切っています。

文字列を区切り文字として指定する場合は、Googleスプレッドシートと少し違いがあります。

Googleスプレッドシートの場合は、基本は 自動で単体文字区切りとなる為、

このように指定すると

といった具合に一塊の文字列で区切るつもりが、個々の文字

半角スペース,  a, n, d

を区切り文字として扱い、これら全てで区切られてしまいます。

Googleスプレッドシートで " and " （前後に半角スペースのある and ）を一塊の区切り文字として扱う場合は、

このように 第3引数（各文字での分割）を falase と指定する必要があります。※第3引数は デフォルトが true という扱い。

③複数の単文字での横区切り

では、逆に ExcelのTEXTSPLIT で複数の区切り文字を指定したい場合はどうすればよいか？

区切り文字を複数指定したい場合は 配列にする必要があります。

上記は : コロン と , カンマを (横)区切り文字に指定する為に

｛ ":" , "," }

と中カッコを使って配列指定しています。配列は 縦、横どちらでもOKなので、

｛ ":"  ; "," }

としてもOKです。

わざわざ ; で縦配列にする人はいないでしょうが、これは下に記述している ⑤ の 複数の区切り文字をセル範囲指定するケースで、縦横の範囲気にせず指定できる利点になります。

④区切り文字をセル参照する場合（1つの単文字）

このケースはどちらも違いはないです。普通にセル参照で区切り文字として使えます。

注意点として、空白セルを 区切り文字指定として参照した場合はエラーとなります。

⑤区切り文字をセル参照する場合（複数の単文字）

複数指定の時のセル参照ですが、EXCELであれば 自動で配列として扱われるので、B14:B15と指定するだけで OK。

ちなみに、区切り文字のセル参照は 縦1列、横1行といった 一次元配列縛りはないので、普通に 縦横に広がる範囲を指定しても問題ないです。

ただし上にも書きましたが、範囲内に空白セルが入っていると エラーになるので、とりあえずダミーでも良いので何か入れておく必要があります。

一方、Googleスプレッドシートは 配列での区切り文字の複数指定は出来ません。

しかし、③の時と同じように SPLIT関数 の第2引数（区切り文字）は、 デフォルトは個々の単文字で区切る処理となるので、参照した2つのセルを区切り文字なしで結合してしまえば ＯＫです。

【余談】 Googleスプレッドシートの CONCATENATE関数は EXCELと違う

ここでは文字の連結に CONCATENATE関数を使っています。

Excelでは、CONCATENATE の後継（拡張版）が CONCAT関数となっており、旧関数のCONCATNATEの出番は現在はありませんが、Googleスプレッドシートだと扱いが違います。

Googleスプレッドシートの場合は、CONCAT関数 の上位互換がCONCATENATE関数であり、 

という理解になります。まぎらわしい・・・。

一応、Googleスプレッドシートの CONCATには Arrayformulaが使える という利点がありますが、それだったら普通に & で結合するよってなりますよね。

文字列連結は、 連結文字を空文字とした　JOINや TEXTJOINを使う方法もあります。

⑥複数の文字列 で区切りたい場合 の違い

では、複数の 単文字ではなく 複数の文字列 で区切りたい 上のようなケースはどのように記述すればよいでしょうか？

Excelの場合は 配列指定なので簡単です。複数の単文字を区切り文字に指定する時と同じように 配列としてカンマ区切りで並べるだけでOK。

Q. Googleスプレッドシートで 2つの「文字列」で区切りたい

一方 Googleスプレッドシートの場合は苦戦します。上記と同じことを実現したい場合、どのような式を作ったらよいでしょうか？

久しぶりに（ミニ）お題です。自信のある人は考えてみましょう。

↓↓↓

回答は以下

↓↓↓

A. Googleスプレッドシートで 2つの文字列で SPLITする方法

回答です。

上でも述べましたが Excelと同じように 区切り文字を配列化してもダメです。

Arrayformulaを組み合わせても 上のように 横配列では 一つめの区切り文字しか使われず、縦配列の場合は上に 一つ目の " and " で区切った結果、下に " or " で区切った結果がそれぞれ展開されるだけｗ

Googleスプレッドシートでは 複数の 文字列を区切り文字に指定する場合は、SPLITだけでは対応できず 関数を組み合わせて対応する必要があります。

1つ目の方法は、2段階SPLIT方式です。

1. 最初に " and " でSPLITしたものを TRANSPOSE で縦並びに変換

2. さらに縦並びにした 配列を Arrayformula + SPLIT で今度は " or " で分割

3. これを FLATTENで 縦1列にしたものを

4. LAMBDA で arrと置いてFILTERで空白除去してから

5. 再TRANSPOSEで横並びにもどす

うーん、Excelのシンプルな処理が Googleスプレッドシートではとんでもない工数です。。

もう一つは、先に 区切り文字として使いたい 2つの文字列を、使われてない適当な単文字（記号）に変換してしまう方法。 こっちの方がシンプル。

REGEXRELACE を使って  " and " または " or " を "_" に変換してから "_" で SPLITしています。

さすが 文字列変換最強の REGEXREPLACE 、とにかく頼れる関数ですｗ （そのうち特集組まないと）

REGEX系は 正規表現 が使えるんですが、正規表現において 複数の文字列を または で指定したい場合は | （パイプ）を使います。

Excelだと 大変な 置換対象の文字列を複数指定するケース（〇〇と▲▲を ××に置換）も、正規表現が使えると簡単に対応できます。

こちらの方法が圧倒的に簡単ではありますが、正規表現として処理をしているので、メタ文字（正規表現において 特別な意味を持つ文字・記号）が登場する場合はエスケープ処理するといった注意が必要です。

TEXTSPLIT と SPLIT で 共通する 第2引数 （横方向への）区切り文字 だけでも、結構仕様が違うことがわかりました。

第３引数 row_delimiter ・・・　縦（行方向）の区切り文字 （省略可）

EXCEL のTEXTSPLITは、なんと 横方向区切り文字とは別に、縦方向区切り文字も指定できます。それが、この第３引数です。

Googleスプレッドシートの SPLIT関数には無い機能ですね。

第2引数を 空 にすることで、縦方向にのみ 分割されます。使い方は 横方向の区切りと同じですね。

②の 単文字でなく 文字列 を区切り文字に使うケースや ③の複数の区切り文字を配列指定するケースも 同様です。

第2引数、第3引数の 横・縦の区切り文字は、必ずどちらか指定する必要があります。両方省略 　=TEXTSPLIT(A2,,) 　はエラーとなります。

TEXTSPLITは 縦横区切りで 領域展開（スピル）できる

第2引数を省略した 第3引数（縦分割）のみ使う例をまずは紹介しましたが、TEXTSPLITの真骨頂は、画像のような 横区切り文字、縦区切り文字 両方を使って 縦横両方に 配列展開が出来ることです。

これだけのシンプルな式で、実現できちゃうのは凄いですね。

ありがちな セル結合の中に、セル内改行で入った複数のデータ。しかも、区切りのスペースも半角だったり全角だったりする上に、金額（数値）に単位（円）が付いてる。

こんな扱いにくいデータだって、

{" ","　","円"} で横区切り　※半角スペース、全角スペース、円
CHAR(10) で縦区切り　※改行コード

と指定してあげれば、一発で使いやすいデータテーブルに変換出来ちゃいます。

【余談】数式の中で「改行」を指定する方法

また 話が脱線しますが、数式の中で改行を指定する方法は幾つかあります。

1. CHAR(10)　改行コードを使う

2. セル内改行を入れたセルを参照させる

3. "(Alt+Enter)" で直接改行を入れる

普通は 1の CHAR(10)を使いますが、実は直接改行を入れることも出来るんですねー。

でも、上のように Alt+Enter すると式内のその場所が改行されるんで、ちょっとカッコ悪いかも。

この数式の中で改行を使う（指定する）方法は、 Googleスプレッドシートでも同じように使えます。

TEXTSPLIT は分割後の数字を文字列として返す

TEXTSPLITの結果の数字部分、右寄せになっているような・・・。って思いますよね。

ExcelのTEXTSPLITには 分割後に数字のみセルがあっても 文字列として返す、という特性があるようです。

だから、上記のように分割した数字をそのまま SUMに入れても残念ながら数値として扱われず計算できません。

※ +-*/ といった演算子を使った計算なら 数値として扱われます。

この出力結果の数字の扱いは Googleスプレッドシートと違っていて、Googleスプレッドシートの SPLIT関数の場合は 自動で数値化されます。

上のように 0始まりの数字は、数値化にともない 頭の 0が 消えちゃいます。

もちろん、分割後の数字で計算をしたい場合は Googleスプレッドシートの方が簡単でよいですし、逆に上のように勝手に 0埋めが消されると困るといった場合は Excelの方が ありがたいってことで、これはどっちが良いということは無く ケースバイケースって感じですね。

これを引数で制御できたら良かったんですがｗ

Excelでは出来る 縦横に展開させる 分割を Googleスプレッドシートで実現するにはどうすればよいか？

は少し長くなるので、次週に回すとして先に 残りの引数を見ていきましょう。

第4引数 ignore_empty ・・・　空のセルを削除するか？

これは、区切り文字が連続していた時に 空白セルとして出力するか、空白セルは削除して（詰めて）出力するかを設定する引数です。

上の画像だと、第4引数を設定しない時は 1行開け（改行2連続）の箇所が 空白セル（グレーの箇所）として出力されますが、 第4引数を TRUE指定した場合は、空白箇所が左に詰まっているのがわかりますね。

縦方向に分割の際も同様に、第4引数 TRUE指定で 上に詰まります。

この設定は GoogleスプレッドシートのSPLITにも 第4引数として存在するんですが・・・。

ということで、省略時（デフォルト）が逆なんですよね。。この関数においては 後追いの EXCEL（MS）側が揃えればいいのに・・・。

こんな小さいところでも、Google と Microsoft の水面下のバチバチを感じさせますねｗ　（AI合戦に比べるとすげーしょぼい争いですが）

第5引数 match_mode ・・・　大文字と小文字を区別するか？

第5引数は マッチモードです。 書いてある通り「区切り文字」の大文字と小文字を区別するか・しないか？ という設定で、

という切換えになっています。

EXCELは そもそも大文字・小文字を区別しない

そもそも、なんで 大文字小文字の区別って話が出るかというと、EXCELの イコール式による 文字列の一致判別は 大文字小文字を区別しないからです。

="and"="AND" 　・・・　これは TRUEを返す

大文字小文字を区別した VLOOKUPやMTACH はどうすりゃいいんだ問題は、 EXCEL界では昔からEXCELではあるあるネタでした。

ちなみに、Googleスプレッドシートの一致判定はもっと雑なんで、それに比べれば全然マシなんですけどねｗ

一致に関する Googleスプレッドシート と EXCELの比較は、XLOOKUPの記事で触れています。

でも、通常は 記号を指定するケースの多い 区切り文字で、大文字・小文字の区別が切り替え出来ても・・・。別にって感じです。

TEXTSPLITじゃなくて、XLOOKUP、XMATCH の一致モードに加えてくれりゃ良かったのに。

しかも 使用例の画像を見るとわかりますが、マッチモード 1を指定して 大文字・小文字区別なしとした場合、

このように 分割された後の結果が 全て小文字に変わっちゃいます。
マジ使えません。

この TEXTSPLITの仕様を決めた担当者は、「これで皆喜んでくれるぞー」って思ったんでしょうか？ 同僚のトンカツ弁当にこっそりアジフライを追加する黒沢 の気分だったんでしょうかｗ

ワイルドカード使えるようにするとか、区切り文字として指定した文字の後ろ（前）で区切りるとか、もっと便利なモード設定がありそうなんですけどね。

この引数は Googleスプレッドシートの SPLIT関数にはありません。

第6引数 pad_with ・・・　空きセルを埋める文字。

pad_with は 、これまで Excel新関数シリーズで取り上げてきた 配列操作系の関数でも登場しましたね。埋める文字です。

上のように縦横両方の区切り文字を指定した場合、分割した結果は 列方向と行方向展開されます。

横区切り文字の数に差異があった場合、つまり 横方向に 4セル分展開される行があったり、3セル分展開される行があったりすると、1つの配列（長方形）として見た時に 行によって不足部分（欠けた部分）が出来てしまいます。

この不足部分のセルはそのままだと #N/A エラーとなるのですが、ここのエラーを埋めるのが pad_with です。

というわけで、不足部分が発生しない 横区切りのみ、縦区切りのみ の分割の場合は不要です。縦横分割の時だけ使うモノです。

画像のように "" で空文字としてあげるケースが多いでしょう。便利ですね。

次回、TEXTSPLIT 応用編！

今回のTEXTSPLIT 基本理解では、6つの引数 をGoogleスプレッドシートのSPLIT関数との比較を交えながら紹介しました。

あまり他のサイトでは触れていない

この辺りにも触れたので、基本編とはいえ 既に活用している EXCELユーザーにも少しは役に立ったでしょうか？

機能が豊富で Googleスプレッドシートの SPLIT関数と違う点も多く、ちょっと説明が長くなってしまいました。応用編については 次回とします。

• SPLIT関数で TEXTSPLITの縦横区切りを どう実現するか？

• 縦横区切りを得た TEXTSPLIT その代償で失ったものとは？

• TEXTSPLIT の活用例・応用例 （分割だけじゃない）

この辺りを 次回の応用編では取り上げたいと思います！