TEXTSPLIT関数を使えば、記号などで区切られた文字列を効率的に分割することができます。
| =TEXTSPLIT(文字列, 列の区切り文字, 行の区切り文字, 空白を無視する, 大文字と小文字の区別) |
|---|
| 文字列を指定した区切り文字で分割し、複数のセルに表示させる。列の区切り文字を指定すれば、横方向のセルに展開され、行の区切り文字を指定すれば、縦方向のセルに展開される。 |
横方向のセルにデータを分割する
たとえば、CSVファイルなどの1行のデータはコンマ（,）で区切られています。このデータをコンマ（,）で分割し、1つ1つのセルに分けたいと思います。
TEXTSPLIT関数で、一つ目の引数には、分割したい文字列を指定し、二つ目の引数に分割の基準となる区切り文字をダブルクォーテーションで囲んで記載します。ここでは、コンマ（,）を指定します。
=TEXTSPLIT(A2,”,”)
これで、コンマ（,）で区切られた1つ1つのデータをセルに分けることができます。この数式では、「列の区切り文字」をしているため、横方向のセルに分割したデータが表示されています。
この数式を下にコピーすれば、下のデータも分割することができます。
TEXTSPLIT関数では、データを格納するためのセルに既にデータが入っていると、スピルというエラーが表示されます。たとえば、以下ではD列に「あ」というデータが既に入力されているためエラーが発生しています。
これでは、分割したデータが表示されないので、出力するセルは空白にするようにしましょう。
縦方向のセルにデータを分割する
縦方向のセルにデータを分割したい場合は、二つ目の引数は空白にして三つ目の引数に区切り文字を入れます。たとえば、以下のような読点で区切られた顧客一覧を縦方向のセルに分割するには、三つ目の引数に区切り文字の読点を記載します。
=TEXTSPLIT(B2,,”、”)
そうすると、縦方向のセルに顧客名が表示されます。
複数の区切り文字を指定する
TEXTSPLIT関数では複数の種類の区切り文字を指定して、文字列を分割することができます。たとえば、このような部署名と名前が記載された文字列を部署名、姓、名の3つに分けます。
ここでは区切り文字としてコロン（：）と空白を基準に分割します。この際、二つ目の引数には、中かっこ（{}）で囲んで、コロン（：）と空白を入力します。これで、2つの区切り文字を基準として分割することができます。
=TEXTSPLIT(A2,{“：”,” “})
改行でデータを分割する
TEXTSPLIT関数では、改行の入ったデータを分割することもできます。この際、区切り文字にはCHAR関数を使って改行を指定します。
| CHAR(文字コード) |
|---|
| 指定された文字コードに対応する文字を返す。文字コード10は改行を意味する。 |
たとえば、このように1つのセルに改行で並べられた名前を分割するには、二つ目の引数にCHAR(10)と入力します。
=TEXTSPLIT(B2,CHAR(10))
これで1つのセルに1つの名前を表示させることができます。
分割した文字列を数値データに変換する
TEXTSPLIT関数は、文字列を表示させる関数であるため、数値であっても文字列データとして扱われます。たとえば、以下の表では、田中さんの希望年収に対して求人案件の年収の条件が合致するか判断するため、E列に数式を入力しています。B列の年収の記載をC、D列に年収下限と年収上限に分割し、希望年収に合致するか判定します。
TEXTSPLIT関数を使って波線（~）で分割するように数式を入力します。
そうすると、年収下限と上限に分けることができました。しかし、E列はすべて✕になっており、正しく条件合致が判定されていません。
これは、TEXTSPLIT関数で分割したデータが文字列であるため、希望年収との大小の比較ができないためです。これを解決するには、TEXTSPLIT関数の数式をVALUE関数で囲みます。
| VALUE(文字列) |
|---|
| 文字列を数値に変換する。 |
=VALUE(TEXTSPLIT(B5,”~”))
これで、年収下限・上限のデータが数値に変換され、E列の数式で条件に合致するか判断されます。
コメント