Excel(エクセル)の新しい関数、TEXTSPLIT関数(テキストスプリット)をご存知でしょうか。
TEXTSPLIT関数は、Microsoft 365、Excel2019以降のバージョンで利用できる新関数です。
この関数は、「－」「,」「(スペース)」などのキーとなる文字をキーにして文字を分割する関数です。
この記事では、TEXTSPLIT関数の使い方と、便利な事例を紹介します。
TEXTSPLIT関数とは？
TEXTSPLIT関数とは、キーとなる文字を指定して文字を分割する関数です。
TEXT(テキスト)が「文字」、SPLIT(スプリット)が「分割」を意味していることからも、どんな関数なのかイメージしやすいと思います。
例えば、この関数を使うと、ハイフンなどの区切り文字をキーに、B列の文字を分割し、C～E列に表示させることができます。(例：『090-888-777』⇒『090』『888』『777』)
上の図の場合、B列にTEXTSPLIT関数は挿入していますが、その結果はスピルとして表示され、C列、D列、E列に表示されます。
セルから特定の文字を抽出することができるLEFT関数、RIGHT関数、MID関数なども便利です。
ただ、キーとなる文字が決まっているときは、TEXTSPLIT関数の方が一瞬で文字を分解することができます。
TEXTSPLIT関数の書式と引数
エクセルのTEXTSPLIT関数の書式(構文)と引数を紹介します。
=TEXTSPLIT(文字列、列区切り、[行区切り]、[空白処理]、[一致モード]、[列数不足時の処理])
引数は６つありますが、基本的には１つ目と２つ目の引数のみ使用します。[]で囲っている３つ目以降の引数は省略可能で、特殊なケースでしか使用しません。
６つの引数の内容は以下のとおりです。
- 第１引数「文字列」・・文字を分解したいセルを指定します。(例：A3)
- 第２引数「列区切り」・・列ごとに区切るためのキーとなる文字を指定します。(例：”-”)
- 第３引数「行区切り」・・省略可能です。分割した文字を下の行に入力するためのキーとなる文字を指定します。(例：”:” ”-”)
- 第４引数「空白処理」・・連続する区切り文字を無視する場合は「TRUE」,空のセルを作りたい場合は「FALSE」を指定します。FALSEの場合は省略可能です。
- 第５引数「一致モード」・・大文字と小文字を区別する場合は「0」、区別しない場合は「１」を指定します。「0」の場合は省略可能です。
- 第６引数「列数不足時の処理」・・文字を分割したとき、横方向のセルがしたときに表示する値を指定します。省略すると「#N/A」を指定したことになります。
TEXTSPLIT関数をセルに挿入すると、書式と引数が英語で表記されますが、日本語に変換すると上記のとおりです。
- 第１引数「Text」＝「文字列」
- 第２引数「Col_delimiter」＝「列区切り」
- 第３引数「Row__delimiter」＝「行区切り」
- 第４引数「Ignore_empty」＝「空白処理」
- 第５引数「Match_Mode」＝「一致モード」
- 第６引数「Pad_With」＝「列数不足時の処理」
また、TEXTSPLIT関数でよく利用される代表的な区切り文字には以下のようなものがあります。
- - ハイフン
- / スラッシュ
- 、 カンマ(コンマ）
- 全角スペース、半角スペース
- 。 句読点
- () 括弧
以下のように、配列定数「{}」を使用することで、複数の区切り文字(例：「/」と「-」）を使って文字を分割することもできます。(具体的な使い方は別途紹介）
=TEXTSPLIT(B6,{"/","-"})
TEXTSPLIT関数で文字を分割する方法
実際にTEXTSPLIT関数を使う方法を解説します。
文字を分けたいセルの中に、区切り文字が１つある場合と、複数個ある場合で使い方が異なります。
区切り文字が１つの場合
TEXTSPLIT関数を使って、「060-8888-7777」を「060」「8888」「7777」に分割する方法を紹介します。
手順１．TEXTSPLIT関数の第１引数を指定
TEXTSPLIT関数を挿入して、第１引数「文字列」に分割したい文字が入力されているセルB3を選択します。
=TEXTSPLIT(B3,
手順２．TEXTSPLIT関数の第２引数を指定
続いて、第２引数「列区切り」を指定します。文字の「-」(ハイフン)をキーに文字を分けたいので、ダブルクォーテーションで囲って、以下のように入力します。
=TEXTSPLIT(B3,"-")
手順３．TEXTSPLIT関数を確定
Enterキーを押して、TEXTSPLIT関数を確定させます。そうすると、「060-8888-7777」が「060」「8888」「7777」の３つに分割されます。
TEXTSPLIT関数を挿入したのはC3セルですが、分割された文字は、C3～E3セルに表示されます。このような数式の結果が他のセルに表示される拡張機能のことをスピルと呼びます。
分割したい文字が複数ある場合は、TEXTSPLIT関数をコピーして貼り付けすれば、一瞬で簡単に文字をまとめて分割することができます。
D3セル、E3セルの数式バーを確認すると、TEXTSPLIT関数の数式がグレーで表示されているとおり、セルに値が入力されているわけではありません。スピルにより結果が拡張表示されているので、Deleteキーで削除することはできません。
スピル機能が不自由に感じる場合は、TEXTSPLIT関数を使って文字を分割した後、結果をコピーして、値で貼り付け直してしてください。
区切り文字が複数個ある場合
TEXTSPLIT関数を使って、「123/158-135」を「123」「158」「135」に分割する方法を紹介します。
上記で説明した例とは異なり、区切り文字が「/」と「-」の複数個(２つ )ある場合の使い方です。
この場合、第２引数に配列数式の「{}」を使用し、「/」と「-」をダブルクォーテーションで囲って、コンマで区切ります。
=TEXTSPLIT(B3,{"/","-"})
第２引数の入力を拡大すると以下のとおりです。複雑な入力に感じるかもしれませんが、構造はシンプルです。カンマを挟めば、３つ、４つ以上の区切り文字を指定することもできます。
TEXTSPLIT関数を確定させます。そうすると、「123/158-135」の文字列が、スピル機能によってC3～E3セルに「123」「158」「135」の３つに分割されます。
TEXTSPLIT関数でエラーが表示される場合
TEXTSPLIT関数を確定した際、結果が拡張表示されるセルに文字が入力されて埋まっている場合は、下の図のように「スピル!」という数式エラーが発生します。
この場合は、E3セルの文字を削除すればエラーは解消されます。
TEXTSPLIT関数の事例を紹介
TEXTSPLIT関数の事例を紹介します。
上記では、基本的な使い方を紹介しましたが、３つ目以降の引数を指定した応用編の実例も解説します。
事例１：３つ以上の複数の区切り文字で分割
TEXTSPLIT関数を使って、３つ以上の複数の区切り文字をキーに文字を分割します。
「/」「-」「_」の区切り文字をキーにして、「123/45-AB_DE」の文字列を、「123」「45」「AB」「DE」の４つに分割することができます。
=TEXTSPLIT(B3,{"/","-","_"})
事例２：特定の文字を削除
TEXTSPLIT関数を使うと、指定した区切り文字は消えます。その特徴を利用して、不要な文字を削除する置換方法を紹介します。
たとえば、コードの末尾についている「-A」だけを消したい場合、以下のTEXTSPLIT関数でコードの一部を削除することができます。
=TEXTSPLIT(B3,"-A")
事例３：セル内改行された文字を行ごとに分割
TEXTSPLIT関数でセル内で改行された文字列をセルごとに分割することができます。
下の図のように、セル内で改行された文字に対して、以下のTEXTSPLIT関数を使うと、横方向(水平)に１行ごとの文字を分割して表示することができます。
=TEXTSPLIT(B3,CHAR(10))
ちなみに、CHAR関数の改行コード「10」は、”改行”を意味しています。
もし、縦方向にセル内改行の文字を縦方向(垂直)に分割したい場合は、TEXTSPLIT関数の第２引数を空白にして、第３引数にCHAR(10)の改行コードを挿入します。
=TEXTSPLIT(B3,,CHAR(10))
事例４：第３引数「行列区切り」を使用
TEXTSPLIT関数で分割した文字を縦方向(垂直方向)に表示することができます。
TEXTSPLIT関数の第２引数「列区切り」を空白にし、第３引数「行区切り」に区切り文字を指定すれば、下の図のように、下方向に分割できます。
=TEXTSPLIT(B3,,"-")
事例５：第４引数「空白処理」を使用
TEXTSPLIT関数の第４引数「空白処理」で「TRUE」か「FALSE(もしくは省略)」を指定することで、区切り文字が連続する場合の分割の方法を変えることができます。
「123//158」の連続した区切り文字が含まれる文字列に対して、以下のTEXTSPLIT関数を挿入します。
=TEXTSPLIT(B3,"/",,TRUE)
=TEXTSPLIT(B3,"/",,FALSE)
上の表の３行目が「TRUE」、4行目が「FALSE」を選んだ場合の結果です。
第４引数を「TRUE」を選ぶと、連続する区切り文字を１つの区切り文字として文字が分割されます。一方、４引数で「FALSE」を選ぶと、上のD4セルのように、間に空白セルが挿入されます。
事例６：第５引数「一致モード」を使用
TEXTSPLIT関数の第5引数「一致モード」で「0(もしくは省略)」か「1」を指定することで、区切り文字の大文字、小文字を区別するか/しないかを切り換えすることができます。
例えば、「1a23A45」の文字に含まれる「A」と「a」を区別して文字を分割する場合は「0」、区別しない場合は「１」を指定します。
=TEXTSPLIT(B3,"A",,,"1")
=TEXTSPLIT(B3,"A",,,"0")
事例７：VALUE関数を組み合わせて、数値に変換する
TEXTSPLIT関数で「100-200」を「100」と「200」に分割しても、分割した文字は文字列に変換されるので、SUM関数などに利用できません。
TEXTSPLIT関数で分割した文字を数式や関数に利用したい時は、VALUE関数と組み合わせて利用します。
VALUE関数は、文字列として入力されている数字を数値に変換する関数です。
構文は「＝VALUE(文字列)」と対象のセルを指定するだけの簡単なものです。
下の数式のように、VALUE関数の中にTEXTSPLIT関数を挿入します。そうすると、TEXTSPLIT関数の結果が数値で変換されるため、SUM関数などに利用できます。
=VALUE(TEXTSPLIT(B3,"-"))
(おまけ)LEFT、RIGHT、MID関数は文字を抽出する関数
セルに入力された文字列から、特定の文字だけを抽出したい場合は、MID関数、LEFT関数、RIGHT関数の３つを使用です。
例えば、下の図のように、『３年２組鈴木一郎』の文字から『３年』や、中央の『２組』だけを抽出したり、後半の『鈴木一郎』だけを抽出できます。
TEXTSPLIT関数とは異なり、文字列の左端や右端などから指定の文字数を抽出する関数です。
以下のリンク記事では、MID関数、LEFT関数、RIGHT関数の使い方と事例について紹介していますので、参考にしてください。
(おまけ)TEXTJOIN関数は文字を結合する関数
TEXTSPLIT関数は文字を分割する関数です。
一方、TEXTJOIN関数は、同じ区切り記号やスペース(空白)を複数回使って、文字列を結合するときに便利な関数です。
＝TEXTJOIN(区切り文字,空のセルの処理,文字列１,文字列２・・・)
下の表のE列に以下のTEXTJOIN関数を挿入します。そうすると、B列～D列の文字が「+」の区切り文字が挟まって結合されています。また、TEXTJOIN関数の先頭に「＆」演算子を使うことで、セルの先頭に「＝」を挿入することもできます。
="="&TEXTJOIN("＋",,B3,C3,D3)
