※本記事には広告・アフィリエイトリンクが含まれます。
「営業部,田中,東京」のように、1つのセルにカンマ区切りで入っている文字を、別々のセルに分けたいことがあります。Excelの「区切り位置」機能でも分けられますが、元のセルを直したときに結果も変わってほしいなら、数式で分ける方法があります。それがTEXTSPLIT関数です。
この記事では、Microsoftサポートの「TEXTSPLIT 関数」のページだけをもとに、6つの引数と、列・行の分け方、空のセルや#N/Aの扱いを整理しました。
TEXTSPLIT関数とは
どんな関数？
Microsoftサポートでは、TEXTSPLIT関数は「列と行の区切り記号を使用してテキスト文字列を分割します」と説明されています。
さらに、テキスト列変換ウィザードと同じように動作するけれど、数式形式で動作する関数だとされています。列をまたいで分割したり、行単位で下に分割したりできます。また、TEXTJOIN関数の反転だ、とも書かれています。TEXTJOIN関数が複数のセルの文字を区切り記号でつなぐのに対し、TEXTSPLIT関数は1つの文字列を区切り記号で分ける、という関係です。
使えるExcel
Microsoftサポートのページの「適用先」には、次のExcelが挙げられています。
| 適用先 |
|---|
| Excel for Microsoft 365 |
| Excel for Microsoft 365 for Mac |
| Excel 2024 |
| Excel 2024 for Mac |
職場で使っているExcelがこの中に入っているかどうか、まず確認しておきましょう。ファイルを共有する相手が別のバージョンを使っている場合もあるので、共有する前に相手の環境も聞いておくと安心です。
構文
Microsoftサポートに載っている構文は、次の通りです。
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])
角かっこ［］で囲まれている引数は、省略できるものです。
TEXTSPLIT関数の6つの引数
ここからは、Microsoftサポートの説明をもとに、6つの引数を1つずつ見ていきます。
1. text（分割するテキスト）
分割するテキストです。Microsoftサポートでは、指定が必要な引数とされていて、省略できません。セル番号（たとえばA2）を指定すれば、そのセルに入っている文字列が分割の対象になります。
2. col_delimiter（列の区切り記号）
列間でテキストをスピルするポイントを示すテキストです。たとえば「,」を指定すると、カンマのところで文字列が分けられ、右方向のセルへ順番に並びます。
| A2の内容 | 数式 | 結果のイメージ |
|---|---|---|
| 営業部,田中,東京 | =TEXTSPLIT(A2,”,”) | 営業部｜田中｜東京（横に3つのセル） |
3. row_delimiter（行の区切り記号）
行方向にテキストをスピルするポイントをマークするテキストで、省略可能です。たとえば「;」を指定すると、セミコロンのところで下の行に分かれます。
Microsoftサポートの使用例2では、「1,2,3;4,5,6」を =TEXTSPLIT(A2,”,”,”;”) で2×3の配列に分割する例が紹介されています。カンマで列が、セミコロンで行が分かれるので、1行目が「1、2、3」、2行目が「4、5、6」になります。
| 1 | 2 | 3 |
|---|---|---|
| 4 | 5 | 6 |
4. ignore_empty（連続する区切り記号の扱い）
TRUEを指定すると、連続する区切り記号は無視されます。既定値はFALSEで、空のセルが作成されます。省略可能です。
たとえば「営業部,,東京」のように、カンマが2つ続いている文字列があるとします。FALSE（または省略）なら、カンマとカンマの間に空のセルができます。TRUEなら、その空のセルは作られません。
| A2の内容 | ignore_empty | 結果のイメージ |
|---|---|---|
| 営業部,,東京 | FALSE（既定） | 営業部｜（空）｜東京 |
| 営業部,,東京 | TRUE | 営業部｜東京 |
項目の位置をそろえたい表（2番目の項目は担当者、と決まっている表）では、空のセルを残すFALSEのほうが列がずれません。逆に、単語だけを取り出したいときはTRUEが向いています。
5. match_mode（大文字と小文字）
大文字と小文字を区別しない一致を実行するには、1を指定します。既定値は0で、大文字と小文字を区別する一致が行われます。省略可能です。
区切り記号にアルファベットを使うとき、たとえば「x」で区切りたいのに、文字列の中に「X」も混ざっている場合に関係してきます。0（既定）なら「x」だけで分け、1なら「X」でも分けます。
6. pad_with（足りない部分を埋める値）
結果を埋め込む値です。既定値は#N/Aです。
列と行の両方で分割したとき、行ごとに分かれた個数がそろっていないと、足りない場所が出てきます。その場所に入る値が、何も指定しなければ#N/Aになります。Microsoftサポートの例7では、#N/Aエラーを削除するには、IFNA関数を使用するか、pad_with引数を追加する、というヒントが紹介されています。
| A2の内容 | 数式 | 2行目の結果のイメージ |
|---|---|---|
| 1,2,3;4,5 | =TEXTSPLIT(A2,”,”,”;”) | 4｜5｜#N/A |
| 1,2,3;4,5 | =TEXTSPLIT(A2,”,”,”;”,,,””) | 4｜5｜（空） |
区切り記号を複数使う
配列定数で指定する
Microsoftサポートの解説では、複数の区切り記号がある場合は、配列定数を使用する必要がある、とされています。たとえば、コンマとピリオドの両方で分割するには、=TEXTSPLIT(A1,｛”,”,”.”｝) を使用します。
配列定数は、波かっこ ｛ ｝ の中に、カンマで区切って区切り記号を並べたものです。
※この記事では、表示の都合で波かっこを全角（｛｝）で書いています。Excelに入力するときは半角の波かっこにしてください。
仕事のデータで考える
たとえば、取引先から届いた一覧で、区切りに「、」と「・」が混ざっていたとします。
| A2の内容 | 数式 | 結果のイメージ |
|---|---|---|
| 東京、大阪・名古屋 | =TEXTSPLIT(A2,｛”、”,”・”｝) | 東京｜大阪｜名古屋 |
区切り記号をそろえる置換をしてから分けることもできますが、配列定数を使えば、元のデータを書き換えずに1つの数式で分けられます。
列と行の両方で分ける
Microsoftサポートの例7では、=TEXTSPLIT(A2,” “,”.”,TRUE) という数式が紹介されています。スペースで列に、ピリオドで行に分け、TRUEで連続する区切り記号を無視する指定です。
このように、col_delimiterとrow_delimiterの両方を指定すると、1つのセルの文字列を表の形に広げられます。
行方向だけに分ける
col_delimiterを空ける
Microsoftサポートの使用例4では、=TEXTSPLIT(A2,,”.”) という数式が紹介されています。2番目の引数（col_delimiter）を空けて、3番目の引数（row_delimiter）にピリオドを指定しています。
こうすると、横には分けずに、ピリオドのところで下の行へ分けることができます。カンマが2つ続いているのは、2番目の引数を省略しているという意味です。
縦に並べたい場面
たとえば、1つのセルに「会議資料;見積書;請求書」のように書かれたタスクを、縦の一覧にしたいときに使えます。
| A2の内容 | 数式 | 結果のイメージ |
|---|---|---|
| 会議資料;見積書;請求書 | =TEXTSPLIT(A2,,”;”) | 会議資料／見積書／請求書（縦に3つのセル） |
縦に並べておくと、ほかのセルを参照してチェック欄をつけるなど、一覧として扱いやすくなります。
使用例のまとめ
| 使用例 | 数式 | 分け方 |
|---|---|---|
| 使用例1 | =TEXTSPLIT(A2,” “) | スペースで列に分ける |
| 使用例2 | =TEXTSPLIT(A2,”,”,”;”) | カンマで列、セミコロンで行 |
| 使用例3 | =TEXTSPLIT(A2,｛”.”,”-“｝) | 2つの区切り記号で列に分ける |
| 使用例4 | =TEXTSPLIT(A2,,”.”) | ピリオドで行に分ける |
| 使用例6 | =TEXTSPLIT(A2,,｛”.”,”-“｝,TRUE) | 2つの区切り記号で行、連続は無視 |
| 例7 | =TEXTSPLIT(A2,” “,”.”,TRUE) | スペースで列、ピリオドで行 |
使う前に確かめたいこと
結果を置く場所を空けておく
TEXTSPLIT関数は、分けた結果を右や下のセルに広げて表示します（Microsoftサポートでは「スピル」と表現されています）。分けた後の個数ぶん、右や下のセルを空けておくと、表の中の別のデータと重なりにくくなります。
区切り記号を確認する
全角のカンマ「，」と半角のカンマ「,」は別の文字です。データの中でどちらが使われているかを先に見ておき、col_delimiterやrow_delimiterに同じ文字を指定しましょう。混ざっているなら、配列定数で両方を指定する方法があります。
元のデータは残しておく
TEXTSPLIT関数は数式なので、元のセルを書き換えると結果も変わります。元のデータを消してしまうと、結果も表示されなくなります。分けた結果を別のファイルに渡したいときは、元のデータを残したまま作業するのがおすすめです。
TEXTSPLIT関数のよくある質問
「区切り位置」機能と何が違う？
Microsoftサポートでは、TEXTSPLIT関数はテキスト列変換ウィザードと同じように動作するけれど、数式形式で動作する、と説明されています。数式なので、元のセルの内容を変えると結果も変わります。
結果に#N/Aが出たら？
Microsoftサポートでは、IFNA関数を使うか、pad_with引数を追加する方法が紹介されています。
大文字と小文字は区別される？
match_modeの既定値は0で、大文字と小文字を区別する一致が行われます。区別しないなら1を指定します。
TEXTSPLIT関数で1つのセルを分けてみよう
TEXTSPLIT関数は、列の区切り記号と行の区切り記号を指定して、1つのセルの文字列を分ける関数です。空のセルの扱いはignore_empty、大文字と小文字はmatch_mode、足りない部分の値はpad_withで指定できます。まずは手元の小さなデータで、列だけ、行だけ、両方の3つの分け方を試してみてください。
