Excel（エクセル）には400種類以上の関数があり、バージョンアップごとに新しい関数が登場していることをご存じでしょうか。
今回はExcelのバージョンがMicrosoft365で2022年8月下旬ごろから使用できるようになった「テキストの分割」ができる関数についてご紹介します。
WEBからコピーしたデータや、csvデータを分割する際、従来は《データ》タブ-《データツール》グループの《区切位置》を利用して「区切り記号」を指定してテキストを分割したり、VBAを作成して行なっていたテキストの分割を関数で実施する方法やポイントをご紹介します。
監修：みずき さん
EXCEL女子の研修講師をしています。Excel研修では「覚える」だけではなく「理解する」ことで、自身の力としてExcelスキルを身につけられる研修を実施しています。研修をするたびにExcelが大好きになり、Excelってすごい！と実感しています。Excelの他には写真が好きで食べ物や風景を撮影しています！
TEXTSPLIT関数とはテキストの分割を行う関数です。
TEXTAPLIT関数を使用すると、元のデータを変更することなくデータの分割を行うことができるため、「区切位置」より汎用性が高く使い勝手がよいというメリットがあります。
TEXTSPLIT関数の基本構文は下記の通りです。
2022年10月現在こちらの関数の引数は、英語表記となっておりますので、その通り記載しています。
=TEXTSPLIT( Text,Col_delimiter,[Row_delimiter],[Ignore_empty],[Match_mode],[Pad_With]）
指定した範囲のデータを、分割して配列として返す早速、使用例を用いながら基本の使い方を確認していきましょう。
【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キーを押し数式を確定
縦と横に分割したテキストが、配列としてスピルを利用して表示されます。
ここではTEXTSPLIT関数の省略可能な3つ引数の使い方を解説します。
黄色のセルには元データ（【A3】セル）のセルのデータを「,」で区切るTEXTSPLIT関数が入力されています。FALSEまたは省略で、データがない場合空白を返します。TRUEを指定するとデータがない場合セルとして出力されず無視されます。
黄色のセルには元データのセル（【A8】セル）のデータを「a」で区切るTEXTSPLIT関数が入力されています。0または省略で、区切り記号の大文字と小文字を区別します。1を指定すると大文字と小文字を区別しません。
黄色のセルには元データのセル（【A13】セル）のデータを横は「，」縦は「：」で区切るTEXTSPLIT関数が入力されています。Pad_Withの引数は、データが不足していた際に、表示するデータを指定することができます。今回は「なし」と指定しています。
ここからは、TEXTSPLIT関数と組み合わせて使用すると便利な関数を使用します。
セル範囲【A1：B7】の表は、総務省が明示している「統計表における機械判読可能なデータ 作成に関する表記方法」の修正前のデータの例を元にした表です。
データの入力ルールに関しては、こちらの記事もご覧ください。
データ入力のルール！Excelの入力方法を守るだけでデータの価値が変わる
セルが結合され、データが1つのセルにまとまっています。このままのデータではBIツールなどに取り込むことができないため、TEXTSPLIT関数を使用してテキストを分割していきます。
年度ごとのデータは「、」で区切られているので、横は「（」で区切り、縦は「、」で区切り、【D2】セルを起点に表示します。
しかし、データを分割した時余計な文字がある場合があります。そんな場合はSUBSTITUTE関数と組み合わせて利用することで不要な文字を空欄に置換します。
（1）【D2】セルに「=TEXTSPLIT(B2,"(","、")」と入力
【B2】セルを横は「(」縦は「、」で分割してください。という意味です。
（2）【D2】セルの数式を「=SUBSTITUTE(TEXTSPLIT(B2,"(","、"),")","")」に変更
TEXTSPLITで分割した文字の「）」を「""」（空白）で置換してください。という意味です。
SUBSTITUTE関数の基本構文を確認してみましょう。
=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象]）
文字列の中の指定した文字を新しい文字で置き換える
（3）Enterキーを押し、数式を確定
「）」が置換されて表示されます。
分割を実施後、分割した一部分の列だけ表示をしたい場合は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関数の基本構文を確認してみましょう。
=CHOOSECOLS(Arry,Col_num1,Col_num2...）
配列または参照から列を返す
（4）Enterキーを押し数式を確定
分割した2列目のデータだけが表示されます。
TEXTSPLIT関数によって分割された結果は文字列となります。数式で「数値」として扱う場合は、 VALUE関数を使用して数値データに変換します。
セル範囲【A1：C9】の表のC列のテキストを分割して、セル範囲【G2：H9】に表示し、合計値を求めます。
（1）【G2】セルに「=TEXTSPLIT(C2,"/")」と入力
【C2】セルを「/」で分割してください。という意味です。
（2）Enterキーを押し数式を確定
【G2】セルを起点にデータが表示されますが、合計値が計算されません。TEXTSPLIT関数で分割されたデータは「文字列データ」となり関数で計算することができません。
（3）【G2】セルの数式を「」に変更
TEXTSPLITで分割した「文字列」を「数値」に変換してください。という意味です。
VALUE関数の基本構文を確認してみましょう。
=VALUE(文字列）
文字列として入力されている数字を数値に変換
（4） Enterキーを押し数式を確定し、表の範囲にコピー
文字列が数値に計算され、合計値に値が反映されます。
今回は新関数の「TEXTSPLIT関数」の基本から、実務で使用すると便利な関数の組み合わせまでを紹介しました。これらの関数を使いこなすことで、データクレンジングなどの作業が効率化され、業務の幅も広がります。
当社では100％有資格者であるEXCEL女子が講師の「Excel研修」を実施しており、Microsoft365に対応して研修も行っております。オンライン、対面など受講形式も選べますので、まずはお気軽にご相談ください。