いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

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

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

  • FILTERXML関数などを使って、区切り文字による文字列の分割を行うことができます。
  • ただし「<」などの文字が含まれるとエラーになります。区切り文字が連続する場合にはその部分がエラーとして出力されます。

手順

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

f:id:accs2014:20200909115054p:plain:w780

 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関数で横に並べ替えています。


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

f:id:accs2014:20200907214640p:plain:w780

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