（Excel）区切り文字による文字列の分割を関数で行う

　[※2022年12月更新]
　Excelで、関数を使って区切り文字による文字列の分割を行う方法についてです。つまりはワークシート関数によるSPLIT関数の実現です。

※その後ExcelにTEXTSPLIT関数が導入されました。本記事の内容は旧バージョンでは使える機会があるかもしれませんが、基本的に不要となりました。
　TEXTSPLIT関数については次の記事で紹介しています。

手順

　画像ではB3セルに対象となる文字列が入力されています。
　B3セルの文字列を「☆」で区切った結果を得るため、C3セルに次のような式を入力しています。

　C3セル

=TRANSPOSE(FILTERXML(SUBSTITUTE("<r><n>"&B3&"</n></r>","☆","</n><n>"),"r/n"))

「SUBSTITUTE("<r><n>"&B3&"</n></r>","☆","</n><n>")」の部分により、文字列を簡単なXMLデータに置き換えています。
　具体的にB3セルの文字列は次のように置き換えられます。

<r><n>はまち</n><n>えび</n><n>サーモン</n><n>ほたて</n></r>

　あとはFILTERXML関数により各ノード(nタグ)の文字列を抽出しています。
　なお、FILTERXML関数で抽出したデータは縦に並んでしまうのでTRANSPOSE関数で横に並べ替えています。

　確定した結果は次のとおりです。

　文字列操作としては異例のアプローチかもしれませんが、比較的短い式で分割を行うことができます。
　ただし「<」などの文字が含まれるとエラーになるのが難点です。また、区切り文字が連続する場合にその部分がエラーとして出力されます。