Excel（エクセル）のTEXTSPLIT関数とは？指定した区切り文字でテキストを思いのままに分割！

Excel（エクセル）には400種類以上の関数があり、バージョンアップごとに新しい関数が登場していることをご存じでしょうか。

今回はExcelのバージョンがMicrosoft365で2022年8月下旬ごろから使用できるようになった「テキストの分割」ができる関数についてご紹介します。

WEBからコピーしたデータや、csvデータを分割する際、従来は《データ》タブ-《データツール》グループの《区切位置》を利用して「区切り記号」を指定してテキストを分割したり、VBAを作成して行なっていたテキストの分割を関数で実施する方法やポイントをご紹介します。

Excel（エクセル）のTEXTSPLIT関数とは？

TEXTSPLIT関数とはテキストの分割を行う関数です。

TEXTAPLIT関数を使用すると、元のデータを変更することなくデータの分割を行うことができるため、「区切位置」より汎用性が高く使い勝手がよいというメリットがあります。

Excel（エクセル）のTEXTSPLIT関数の使い方

TEXTSPLIT関数の基本構文は下記の通りです。
2022年10月現在こちらの関数の引数は、英語表記となっておりますので、その通り記載しています。

横方向にデータを分割する方法

早速、使用例を用いながら基本の使い方を確認していきましょう。

【A1】セルに入力されているカンマ区切りのデータを分割し、【A4】セルを起点として表示します。

（1）分割後のデータを表示したい【A4】セルを選択

（2）数式バーに「=TEXTSPLIT(A1,",")」と入力
A1セルを、横区切り文字「,」で分割してください。という意味です。 

（3）Enterキーを押し数式を確定
分割したテキストが、配列としてスピルを利用して表示されます。こちらは、Microsoft365に新たに追加された機能「スピル」の仕組みが含まれております。

「スピル」については、以下記事をご確認ください。
スピルとは？Excelの常識が変わる革新的な機能！ 

縦方向にデータを分割する方法

TEXTSPLIT関数では縦方向にデータを分割することも可能です。第3引数の、Row_delimiterで縦区切り文字を指定します。

【A1】セルに入力されているカンマ区切りのデータを横方向は「,」、縦方向は「:」で分割し、【A4】セルを起点として表示します。 

（1）分割後のデータを表示したい【A4】セルを選択

（2）数式バーに「=TEXTSPLIT(A1,","」と入力
A1セルを、横区切り文字「,」で分割するという意味です。 

（3）続けて「,":")」と入力
縦切り文字「:」で分割するという意味です。 

（4）Enterキーを押し数式を確定
縦と横に分割したテキストが、配列としてスピルを利用して表示されます。

↑　記事TOP

Excel（エクセル）のTEXTSPLIT関数の省略可能な引数について

ここではTEXTSPLIT関数の省略可能な3つ引数の使い方を解説します。

Ignore_empty（空白処理）

黄色のセルには元データ（【A3】セル）のセルのデータを「,」で区切るTEXTSPLIT関数が入力されています。FALSEまたは省略で、データがない場合空白を返します。TRUEを指定するとデータがない場合セルとして出力されず無視されます。

Match_Mode（一致モード）

黄色のセルには元データのセル（【A8】セル）のデータを「a」で区切るTEXTSPLIT関数が入力されています。0または省略で、区切り記号の大文字と小文字を区別します。1を指定すると大文字と小文字を区別しません。

Pad_With（列数不足時の処理）

黄色のセルには元データのセル（【A13】セル）のデータを横は「，」縦は「：」で区切るTEXTSPLIT関数が入力されています。Pad_Withの引数は、データが不足していた際に、表示するデータを指定することができます。今回は「なし」と指定しています。

その他の関数と組み合わせて使う

ここからは、TEXTSPLIT関数と組み合わせて使用すると便利な関数を使用します。

SUBSTITUTE関数と組み合わせる

セル範囲【A1：B7】の表は、総務省が明示している「統計表における機械判読可能なデータ 作成に関する表記方法」の修正前のデータの例を元にした表です。

データの入力ルールに関しては、こちらの記事もご覧ください。
データ入力のルール！Excelの入力方法を守るだけでデータの価値が変わる 

セルが結合され、データが1つのセルにまとまっています。このままのデータではBIツールなどに取り込むことができないため、TEXTSPLIT関数を使用してテキストを分割していきます。
年度ごとのデータは「、」で区切られているので、横は「（」で区切り、縦は「、」で区切り、【D2】セルを起点に表示します。

しかし、データを分割した時余計な文字がある場合があります。そんな場合はSUBSTITUTE関数と組み合わせて利用することで不要な文字を空欄に置換します。

（1）【D2】セルに「=TEXTSPLIT(B2,"(","、")」と入力
【B2】セルを横は「(」縦は「、」で分割してください。という意味です。

テキストが横と縦でそれぞれ分割して表示されますが、「）」が表示されたままです。 

（2）【D2】セルの数式を「=SUBSTITUTE(TEXTSPLIT(B2,"(","、"),")","")」に変更
TEXTSPLITで分割した文字の「）」を「""」（空白）で置換してください。という意味です。 

SUBSTITUTE関数の基本構文を確認してみましょう。

（3）Enterキーを押し、数式を確定
「）」が置換されて表示されます。

CHOOSECOLS関数と組み合わせる

分割を実施後、分割した一部分の列だけ表示をしたい場合はCHOOSECOLS関数を利用することで、表示したい列を指定することができます。

続いて出荷額を表示します。【F2】セルに先ほどと同様のTEXTSPLIT関数とSUBSTITUTE関数を入力します。

（1）【F2】セルに「=SUBSTITUTE(TEXTSPLIT(B5,"(","、"),")","")」と入力
式の考え方は【D2】セルと同様TEXTSPLITで分割した文字の「）」を「""」（空白）で置換してください。という意味です。

（2） Enterキーを押し数式を確定
【F2】セルを起点にデータが表示されますが、今回は結果の1列目は表示する必要がありません。

（3）【F2】セルの数式を「=CHOOSECOLS(SUBSTITUTE(TEXTSPLIT(B5,"(","、"),")",""),2)」に変更
TEXTSPLITで分割した文字の配列の2列目を表示してください。という意味です。

CHOOSECOLS関数の基本構文を確認してみましょう。

（4）Enterキーを押し数式を確定
分割した2列目のデータだけが表示されます。 

 VALUE関数と組み合わせる

TEXTSPLIT関数によって分割された結果は文字列となります。数式で「数値」として扱う場合は、 VALUE関数を使用して数値データに変換します。

セル範囲【A1：C9】の表のC列のテキストを分割して、セル範囲【G2：H9】に表示し、合計値を求めます。

（1）【G2】セルに「=TEXTSPLIT(C2,"/")」と入力
【C2】セルを「/」で分割してください。という意味です。

（2）Enterキーを押し数式を確定
【G2】セルを起点にデータが表示されますが、合計値が計算されません。TEXTSPLIT関数で分割されたデータは「文字列データ」となり関数で計算することができません。

（3）【G2】セルの数式を「」に変更
TEXTSPLITで分割した「文字列」を「数値」に変換してください。という意味です。

VALUE関数の基本構文を確認してみましょう。

（4） Enterキーを押し数式を確定し、表の範囲にコピー
文字列が数値に計算され、合計値に値が反映されます。

Microsoft365の研修もEXCEL女子にお任せください

今回は新関数の「TEXTSPLIT関数」の基本から、実務で使用すると便利な関数の組み合わせまでを紹介しました。これらの関数を使いこなすことで、データクレンジングなどの作業が効率化され、業務の幅も広がります。

当社では100％有資格者であるEXCEL女子が講師の「Excel研修」を実施しており、Microsoft365に対応して研修も行っております。オンライン、対面など受講形式も選べますので、まずはお気軽にご相談ください。