- TEXTSPLIT関数の第1引数として複数のセルを与えた場合、それぞれのセルの文字列を区切り文字で分割した最初の項目しか返ってきません。
- 対策としては文字列を一旦TEXTJOIN関数で連結する方法があります。簡潔ですが合計文字数が長すぎるとエラーになります。
- REDUCE/LAMBDA関数を使う方法もありますが若干式が複雑です。
問題となる状況
画像ではTEXTSPLIT関数を使い、B3:B6の範囲の文字列を区切り文字「☆」で区切った結果を一括して取得しようとしています。
しかし結果は期待と異なるものとなります。確かに4つのセルに対する結果は返っているものの、それぞれのセルを「☆」で区切った最初の項目しか得られていません。
この場合は式をコピーすれば済む話ですが、アンピボットのような操作が面倒になるのが残念なところです。
D3セル
=TEXTSPLIT(B3:B6,"☆")
そこで以下では他の関数を組み合わせた解決法を試してみます。
TEXTJOIN関数を使う方法
TEXTJOIN関数を利用して対象となる文字列に行区切り文字を加えて連結し、TEXTSPLIT関数で再度分割する方法です。
簡潔ではあるものの連結した文字列が数万字に達するとエラーになるのが欠点です。また、行区切り文字には文字列中で使われていない文字を選択する必要があります。
D3セル
=TEXTSPLIT(TEXTJOIN("_",FALSE,B3:B6),"☆","_",,,"") 「TEXTJOIN("_",FALSE,B3:B6)」によりB3:B6の文字列を、区切り文字「_」を挿みつつ連結しています。そしてTEXTSPLIT関数により、「☆」を列区切り文字とし、「_」を行区切り文字として分割しています。
なお、TEXTJOIN関数の第2引数（空のセルを無視）をFALSEにしているためB3:B6の中に空白セルがあっても行を詰めません。ここは欲しい結果によって判断が必要なところです。
列区切り文字のほかに行区切り文字（ここでは「_」）がある場合、連結時の区切り文字をそれに合わせておけば自然にスタックした結果を得ることができます。
式の内容は1つ上のものと全く同じです。
REDUCE/LAMBDA関数を使う方法
LAMBDA関数を使い、各セルごとに分割を実行した結果を得る方法です。
ヘルパー関数はMAPかBYROWが適当に見えますがエラーになるので、REDUCE/VSTACK関数を使い出力を積み重ねていきます。無用な初期配列を設定して削除する作業が生じるのが残念な点です。
なお、対象となるセルに空白セルや空文字列が含まれていると全体が1つのエラーになります。FILTER関数等で対策は可能ですが不必要に長くなりそうなので省略します。
D3セル
=DROP(IFNA(
REDUCE("",B3:B6,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"☆")))),
""),1) REDUCE/LAMBDA関数を使い初期配列となる空文字にa、処理対象となる配列B3:B6にbという名前を付けています。
あとはbの1セルごとにTEXTSPLITを実行してVSTACK関数によりaに順次スタックしていきます。
各行の列数の違いにより#N/Aエラーが生じるのでIFNA関数で空文字列に変換し、最後にDROP関数で先頭行（空文字列だけ）を削除しています。
列区切り文字のほかに行区切り文字（ここでは「_」）がある場合にも対応できます。
D3セル
=DROP(IFNA(
REDUCE("",B3:B6,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"☆","_")))),
""),1)
