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

 Excelで、カンマやハイフンなどで多数に区切られた文字列を分割して横方向のセルに展開する方法についてです。メニューで実行する方法もありますがここでは関数で実現してみます。
 VBAのSplit関数の再現です。

  • MID関数、SUBSTITUTE関数、SEQUENCE関数などの組み合わせにより、文字列を区切り文字で分割して多数のセルに展開することができます。

手順

 B3:B8にカンマ区切りされた文字列が並んでいます。
 ここでC3セルに次のような式を入力します。

f:id:accs2014:20191208165603p:plain:w750

=TRIM(MID(SUBSTITUTE(B3,",",REPT(" ",LEN(B3))),1+LEN(B3)*((SEQUENCE(1,99))-1),LEN(B3)))

 
 まず区切り文字を、元の文字列と長さと同じ長さの半角スペースに置き換えています。そしてMID関数とSEQUENCE関数の組み合わせにより元の文字列の長さごとに区切りTRIM関数でスペースを削除しています。
 アプローチが筋悪なので、MID関数がスペースではなく文字列の途中を区切ってしまわないか気になりますが、これはカンマで区切られた各文字列の数(n1,n2,…)の累積(Σn)が途中で元の文字列の数を超えない限り起こりません。つまり起こりません。

 なお、SEQUENCE関数の引数を「99」としているので値は99個まで(カンマ98個まで)しか拾えません。本来は区切り文字の数を数えて+1すべきですが長くなるので即値にしています。
 また、TRIM関数を用いている関係上、区切られた各文字列の最初と最後のスペースは削られ、途中のスペースは1個に詰められてしまいますので注意してください。
 区切り文字はSUBSTITUTEとREPTの間にある「","」のところで定めていますので、必要に応じ「”_”」「”-”」など適当に変えてください。ただし1文字の区切り文字にしか対応しません。
 

 式の実行結果はこうなります。カンマで区切られた3つの値が3つのセルに分割されています。

f:id:accs2014:20191208165611p:plain:w750

 さらに式を下方向にコピーした結果です。
 画像からわかるように、カンマがつながっている場合は空白セルとして抽出されます。数字は先頭が0でも0が消えずに抽出されます。空白セルでもエラーになりません。

 ちなみに最初の式の中の「B3」を「B3:B8」に直せば(4か所)、1セルへの入力だけで同じ結果になります。