TEXTSPLITの機能をEXCEL2013以降のエクセルで実装する

ツールを開発するときに問題になるのが提供先が使えるかという点です。
弊所はMicrosoft365を導入しているので最新の関数まで使えるのですが、ツールの提供先がエクセルの古いバージョンを使用しているときは新しい関数が使えずツールそのものが使えない場合もあります。

特に、TEXTSPLIT関数は分析にとても使える関数なんですが現在はMicrosoft365を導入していないと使用できず、相手によってツールを提供できない場合があります。
そこで、なんとか同じ機能を前のバージョンでも実装できないか調べてみると、Excel2013から同じ機能を実装する方法がありました（ネタ元はこの記事の最後に付けてます）。

例えばA2処理する文字列があり区切文字を";;"とすると数式は、

=TRANSPOSE(FILTERXML(SUBSTITUTE("<r><n>"&A2&"</n></r>",";;","</n><n>"),"r/n"))

となります。このように、この数式ではFILTERXMLという関数を使います。FILTERXML関数は「指定したXML形式のデータから指定したパスにあるデータを取り出す」ことができる関数のようです。これだけ聞いても割とチンプンカンプンなんですが、要するにXML形式で書かれたデータに含まれる複数の値を取り出せる関数のようです。

これを踏まえて処理を具体的に説明すると、SUBSTITUTE関数の部分で区切文字を適宜のタグに置き換えつつXML形式にし、FILTERXML関数で分割して取り出し、TRANSPOSE関数で取り出した値を列方向に並べています。この数式で複数行の特許分類のデータを処理してみると以下のようになります。

さらに、対象のセルにデータが含まれていないときのエラーを防止するためにIFERROR関数を使った例外処理を入れておいていいです。IFERROR関数は2007から使えるので追加しても問題なさそうですね。

この場合でもちゃんと特許分類を分割してくれています。
以上を踏まえた数式は以下となります。

=IFERROR(TRANSPOSE(FILTERXML(SUBSTITUTE("<A><B>"&A2&"</B></A>",";;","</B><B>"),"A/B")),"")

以上のような数式を利用することで、TEXTSPLITと同様に、特許分類、出願人、発明者などのようにカンマ区切りなどで１つのセルに複数のデータが含まれているデータを分割して分析に使うことができるので、分析できる幅が大きく広がります。

とても役に立つ機能なのでExcel2021以前のバージョンを使われている方はぜひ使ってみてください。

最後に、ネタ元のページは以下です。他のページでも紹介されていたのですがこのページが一番分かりやすかったです。感謝！
（Excel）区切り文字による文字列の分割を関数で行う - いきなり答える備忘録 (officeisyours.com)

今回は以上です。