(Gスプレッドシート)SPLIT関数で区切り文字が連続したら空白セルを返す

 GoogleスプレッドシートのSPLIT関数では、区切り文字を境界として文字列を複数のセルに分割することができます。しかし区切り文字が2つ以上連続する場合でも1つだけと同じ扱いになり、空白セルやエラー値などが返されることはありません(TEXTJOIN関数での「空白を無視」のようなオプションがない)。
 そこでちょっとした準備をして、連続した区切り文字(の間)が空白セルとして返されるようにします。

  • 2つの区切り文字の間にアポストロフィ(')を挿入しておくことにより、SPLIT関数で評価したときに空白セルが返されます。

手順

f:id:accs2014:20200102235824p:plain:right:w550

 B2セルにカンマ区切りの文字列が記入されており、文字列中にはカンマが連続している箇所があります。
 これをSPLIT関数を使って分割するわけですが、カンマに挟まれている部分は空白セルとして返させるものとします。具体的にはカンマが2つ続けば空白セルを1つ返し、カンマが3つ続けば空白セルを2つ返し(以下同様)……とします。

 D4セルに次のように入力します。

=SPLIT(SUBSTITUTE(SUBSTITUTE(B2,",,",",',"),",,",",',"),",")

 引数の境界もカンマなので大変わかりにくい式になっていますが、まず最も内側の「SUBSTITUTE(B2,",,",",',")」により、文字列中の「,,」を「,',」に置き換えています(カンマの間にアポストロフィを挿入している)。
 その外にもう1つSUBSTITUTEがありますが、実は全く同じことをやっています。なぜかというと内側のSUBSTITUTE関数だけではすべてのカンマの間にアポストロフィを挿入できないからです。例えば「,,,,,,」は「,',,',,',」となり、連続するカンマが残ってしまうからです。これはSUBSTITUTE関数の性質によるものですが、もう一度SUBSTITUTE関数で同じ操作を行うことで残った箇所にもアポストロフィを挿入できます。カンマが何個連続していてもSUBSTITUTE関数2回で足ります
 最後に、もっとも外側にあるSPLIT関数で文字列の分割を行っています。
 
 結果は次のようになります。

f:id:accs2014:20200102235829p:plain:right:w750

 なお、空白になっているセルを選択しても数式バーには何も表示されませんが、このセルをISBLANK関数で評価するとFALSEが返りますので、正確にいえば純粋な空白セルとは区別されます。