【Excel新関数】「区切り位置指定」はもう古い？ 「TEXTSPLIT」で複雑な文字列分割を数式1つで完結させる：Tech TIPS

[小林章彦，デジタルアドバンテージ]

連載目次

対象：Excel 2021／2024／365

「TEXTSPLIT」で複雑な文字列分割を数式1つで完結させる
Excelで「姓名の分離」や「ログの整形」に苦労していないだろうか。従来の「区切り位置指定」ウィザードや複雑なLEFT／FIND関数の組み合わせは、データ更新のたびに再作業が必要になる。本Tech TIPSでは、新関数「TEXTSPLIT関数」を使い、複数の区切り文字への対応や、1セル内のデータを一瞬で2次元の表に変換する方法を解説する。画面の住所録は、「テストデータ・ジェネレータ」で生成したダミーデータ。

　「Microsoft Excel（エクセル）」でデータ処理の一つとして文字列を整形することがよくある。その整形作業において、最も頻繁かつ面倒なのが「文字列の分割」だ。これまでは「区切り位置指定」ウィザードや、FIND関数とLEFT／RIGHT関数を組み合わせた複雑な数式が必要だった。

　しかし、Excel 2021で導入された「TEXTSPLIT関数」により、これらの作業は驚くほど簡単になった。本Tech TIPSでは、実務で即座に活用できる具体的な事例とともに、その機能を紹介する。

TEXTSPLIT関数の基本構文

　TEXTSPLIT関数は、指定した区切り文字（デリミタ）に基づいて、文字列を複数のセルに展開（スピル）させる関数である。「区切り位置指定」ウィザードを関数にしたようなものと思うとよいだろう（Tech TIPS「【Excel】一筋縄ではいかない『姓』と『名』を自動で分離する方法」参照のこと）。

　それならTEXTSPLIT関数を使わなくても、「『区切り位置指定』ウィザードでいいのでは？」と思うかもしれない。しかし、「区切り位置指定」ウィザードによる分割は一度きりの処理という欠点がある。それに対し、関数であれば元のデータが追加・変更された場合でも、数式が自動的に再計算され、常に最新の状態が維持される。

　TEXTSPLIT関数の書式は以下の通りだ。

=TEXTSPLIT(文字列, ［列区切り文字］, ［行区切り文字］, ［空のセルを無視］, ［大文字／小文字を区別］, ［埋め込み値］）

TEXTSPLIT関数の書式
「列区切り文字」か「行区切り文字」のいずれかの指定が必要

引数	意味
文字列	分割対象の文字列
列区切り文字	列方向に分割する際の区切り文字（","や" "）
行区切り文字	行方向に分割する際の区切り文字（","や" "）
空のセルを無視	結果に空のセルを含めない場合は「TRUE」を指定。既定値は「FALSE」で空のセルが結果に含まれる
大文字／小文字を区別	大文字と小文字を区別しない一致を実行するには「1」を指定。既定値は「0」で、大文字と小文字を区別する一致を実行する
埋め込み値	行と列の両方向に分割したとき、データ数の不足によって空いたセルに埋め込む値。既定値は「#N/A」エラー
TEXTSPLIT関数の引数

　例えば、「A2」セルに入力されている「織田 信長」を「織田」と「信長」にセルを分けたい場合は、以下のようにすればよい。これで「B2」セルに「織田」、「C2」セルに「信長」が入力される。

=TEXTSPLIT(A2," ")

半角スペースを区切りとして「A2」セルの値をセル分割する

TEXTSPLIT関数の基本的使い方
TEXTSPLIT関数は、第1引数の「文字列」を指定した区切り文字でセル分割するものだ。

　注意が必要なのは、第1引数の「文字列」には、セル範囲（A1:A20）やスピル範囲演算子（A1#）は指定できない、という点だ。複数の行や列に対して適用したい場合は、オートフィルを使用する必要がある。

実務で役立つ3つの活用例

　もう少し具体的にTEXTSPLIT関数の使い方を見ていこう。

複数の区切り文字を一度に処理する

　名前を「姓」と「名」に分けたり、ログを見やすいように分割したりしたい場合にTEXTSPLIT関数を使うと便利だ。ただ、システムによって「カンマ区切り」だったり、「セミコロン区切り」だったり、データの形式が一定でないことは珍しくない。

　そのような場合、TEXTSPLIT関数では区切り文字を配列記号「{ }」で囲むことで、複数の文字を区切り文字にできる。例えば、「A2」セルにある名前の「姓」と「名」の間に区切り文字として、「　（全角スペース）」「 （半角スペース）」「 （半角スペース2つ）」が混在しているような場合は、以下のようにすればよい。

=TEXTSPLIT(A2,{"　"," ","  "})

「A2」セルの値を全角／半角スペースを区切りとしてセル分割する

　同様にログの区切り文字が","や":"の場合は、以下のようにする。

=TEXTSPLIT(A2,{",",":"})

「A2」セルの値をさまざまな区切り文字でセル分割する

　このように区切り文字が混在していても一括で整理が可能である。

複数の区切り文字を一度に処理する（1）
ログなどで複数の区切り文字が使われている場合でも、TEXTSPLIT関数で一度にセル分割が可能だ。

▼

複数の区切り文字を一度に処理する（2）
TEXTSPLIT関数では、区切り文字を「｛｝」で囲むことで複数指定することができる。

空白セルを詰めて抽出する

　データソースに余計な区切り文字が含まれている場合、通常の分割では空白セルが生じてしまう。第4引数（空のセルを無視）に「TRUE」を指定すれば、不要な空白をスキップしてデータを詰めて表示できる。分割してから、後で空白を詰める作業をする必要がない。

空白セルを詰めて抽出する（1）
データの区切り文字として「,,（カンマ2つ）」になっていると、空白のセルが挿入されてしまう。

▼

空白セルを詰めて抽出する（2）
区切り文字として「,（カンマ1つ）」と「,,（カンマ2つ）」を指定して空白セルを回避することも可能だが、第4引数に「TRUE」を指定することで空白セルを無視することも可能だ。

　注意すべきは、元データで行ごとにデータの有無が変わる場合、第4引数を「TRUE」にすると、行によって各カラムのデータがずれていくことだ。全行にわたってデータがない（区切り文字が連続している）ことが保証されているときだけに適用すべきだろう。

1つのセルから「2次元の表」を生成する

　第2引数（列）と第3引数（行）の両方を指定することで「列」と「行」を同時に分けることも可能だ。例えば、1つのセルに詰め込まれた「商品名1:値1;商品名2:値2……」のリストを、列の区切り文字を「:（コロン）」、行の区切り文字を「;（セミコロン）」を指定することで2次元の表に変換できる。これは「区切り位置指定」ウィザードではできない処理だ。

1つのセルから「2次元の表」を生成する
列の区切り文字と行の区切り文字の両方を指定することで、2次元の表に展開することもできる。

【注意】「#SPILL!」エラーと対処法

　TEXTSPLITは、結果を隣接するセルに自動展開（スピル）するため、展開先に既に値が入っていると「#SPILL!」エラーが発生する。数式を入力する右側や下側のセル範囲は完全な空白にしておく必要がある。

　事前に展開後に必要となる列や行を把握して、十分なスペースを確保しておくか、新たなシートに展開するのがよいだろう。