(Excel)TEXTSPLIT関数をスピル(複数のテキストに適用)させる

(長い文字列を渡した場合のエラーと対応について追記しています。ご指摘いただいたm様にお礼を申し上げます)

 ExcelのTEXTSPLIT関数で複数セルを一括して対象とした場合に自然な結果を得る方法についてです。

問題となる状況

 画像では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)

分割後に長い文字列(256文字以上)が含まれる場合

 IFNA関数の第1引数(評価対象)に256文字以上の文字列が含まれると、そのセルがエラーになってしまう(1つの文字列だけを評価する場合はエラーにならず問題はありません)ため、上記の式だとエラーが生じる可能性があります。
 対応としては次のような数式を使う方法が挙げられます。

 D3セル

=LET(x,REDUCE("",B3:B6,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"☆")))),
DROP(IF(ISNA(x),"",x),1)
)

 データの内容に沿って問題となるケースを表現すると、256文字以上の果物名や国名が含まれる場合に、上記の例のようにIFNA関数を使った式を使うとエラーが生じます。
 そこでIFNAの代わりにIF/ISNAでエラーを空文字列に変換しています。式はさらに長くなっていますが、LET関数のおかげでこちらの方が見通しがいいかもしません。



 縦横に分割する例では次のようになります。

 D3セル

=LET(x,REDUCE("",B3:B6,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"☆","_")))),
DROP(IF(ISNA(x),"",x),1)
)