【Excel】TEXTSPLIT関数で文字列をセルに分割する方法!Excelの区切り位置を関数で自動処理

Excelで特定の区切り文字(カンマやスペースなど)で区切られた文字列を、複数のセルに自動で分割したい場面は多いでしょう。手作業でコピー&ペーストを繰り返すのは非効率的です。Excel 2021以降やMicrosoft 365で利用できるTEXTSPLIT関数を使えば、この作業を関数一つで完結できます。

この記事では、TEXTSPLIT関数の基本的な使い方から、区切り文字の指定方法、複数行に分割する方法までを解説します。この関数を使いこなすことで、データの前処理にかかる時間を大幅に短縮できるはずです。

TEXTSPLIT関数の基本とできること

TEXTSPLIT関数は、指定した区切り文字に基づいて、一つの文字列を複数の文字列に分割する関数です。分割された文字列は、配列として返され、Excelの複数のセルに自動的に展開されます。これにより、手作業でのデータ分割作業が不要になります。

この関数は、住所録の「都道府県」「市区町村」「番地」といった要素を分割したり、CSV形式のデータをExcelに取り込んだりする際に非常に役立ちます。Excel 2021、Excel for Microsoft 365で利用可能です。それ以前のバージョンでは利用できません。

TEXTSPLIT関数で文字列を分割する手順

TEXTSPLIT関数では、分割したい文字列、行の区切り文字、列の区切り文字を指定します。基本的な構文は以下の通りです。

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

ここで、textは分割したい元の文字列、col_delimiterは列方向に分割するための区切り文字です。row_delimiterは行方向に分割するための区切り文字で、省略可能です。ignore_emptyは空の要素を無視するかどうかを指定し、match_modeは区切り文字の大文字・小文字を区別するかどうかを指定します。pad_withは、分割時に不足する要素を埋める値を指定します。

1. 分割したい文字列を選択する
   例えば、セルA1に「山田太郎,東京都,中央区,1-2-3」という文字列があるとします。
2. TEXTSPLIT関数を入力する
   結果を表示したいセル(例えばB1)に、以下の数式を入力します。ここではカンマ(,)を列の区切り文字として指定します。
   =TEXTSPLIT(A1,",")
3. Enterキーを押す
   数式を入力したセルから右方向に、分割された文字列が自動的に表示されます。この例では、B1に「山田太郎」、C1に「東京都」、D1に「中央区」、E1に「1-2-3」と表示されます。

区切り文字を複数指定する方法

TEXTSPLIT関数では、列方向および行方向に複数の区切り文字を指定できます。これにより、より複雑な構造の文字列も効率的に分割できます。

列方向に複数の区切り文字を指定する

例えば、住所が「山田太郎|東京都|中央区|1-2-3」のようにパイプ記号(|)で区切られている場合、数式は以下のようになります。

=TEXTSPLIT(A1,"|")

さらに、カンマとパイプ記号の両方を区切り文字として使用したい場合は、配列定数 `{ “|”, “,” }` を使用します。

=TEXTSPLIT(A1,{"|", ","})

この場合、Excelは指定されたどの区切り文字が現れても、そこで分割を行います。例えば、「山田太郎,東京都|中央区,1-2-3」のような文字列も正しく分割されます。

行方向と列方向の両方に区切り文字を指定する

あるセルに複数の情報が格納されており、それを縦横に展開したい場合があります。例えば、セルA1に「商品A,100,東京|商品B,200,大阪」という文字列があり、カンマで列に、パイプ記号で行に分割したいとします。

この場合、列の区切り文字にカンマ、行の区切り文字にパイプ記号を指定します。

=TEXTSPLIT(A1,",","|")

この数式を入力すると、以下のように結果が展開されます。

B1: 商品A
C1: 100
D1: 東京
B2: 商品B
C2: 200
D2: 大阪

空の要素を無視する

区切り文字が連続して出現する場合、空のセルが生成されます。これを無視したい場合は、ignore_empty引数をTRUEに設定します。

例えば、セルA1に「山田太郎,,東京都,中央区」のような文字列があり、連続するカンマによって空のセルが生成されるのを避けたい場合、数式は以下のようになります。

=TEXTSPLIT(A1,",",,TRUE)

この数式により、空のセルが生成されずに「山田太郎」「東京都」「中央区」と分割されます。3番目の引数(行の区切り文字)を省略するため、カンマを2つ続けて入力している点に注意してください。

大文字・小文字の区別

match_mode引数を使用すると、区切り文字の大文字・小文字を区別するかどうかを設定できます。デフォルトでは区別しません(0)。区別したい場合は1を指定します。

例えば、「Apple,Banana,Orange」と「apple,banana,orange」が混在するデータで、区切り文字の「,」は区別せず、大文字・小文字を区別して分割したい場合、以下のように記述します。

=TEXTSPLIT(A1,",",,FALSE,1)

ただし、このmatch_mode引数は、区切り文字自体が複数ある場合に、それぞれの区切り文字に対して大文字・小文字を区別するかどうかを制御するもので、分割対象の文字列に含まれる大文字・小文字を区別するわけではありません。

不足する要素を埋める

pad_with引数を使用すると、分割後に要素が不足した場合に、指定した値で埋めることができます。これは、後続の関数で一定数の列が必要な場合に役立ちます。

例えば、セルA1に「商品A,100」という文字列があり、これを3列に分割したい場合、以下のように記述します。

=TEXTSPLIT(A1,",",,FALSE,,-1)

この数式では、3番目の引数(行の区切り文字)と4番目の引数(ignore_empty)を省略し、5番目の引数(match_mode)も省略しています。結果として、「商品A」「100」「-1」と表示されます。

TEXTSPLIT関数でよくある失敗パターンと対処法

TEXTSPLIT関数は非常に便利ですが、いくつかの注意点や、よくある失敗パターンがあります。それらを理解しておくことで、スムーズに活用できるようになります。

TEXTSPLIT関数が利用できない

原因:
TEXTSPLIT関数は、Excel 2021、Excel for Microsoft 365、およびWeb版Excelの新しいバージョンで利用可能です。それ以前のバージョン(Excel 2019、2016など)ではサポートされていません。

対処法:
1. Excelのバージョンを確認する: Excelの「ファイル」>「アカウント」で、お使いのExcelのバージョンを確認してください。
2. Microsoft 365へのアップグレードを検討する: 最新のExcel機能を利用するには、Microsoft 365サブスクリプションへの加入が推奨されます。
3. 代替手段の使用: 古いバージョンをご利用の場合は、「区切り位置指定ウィザード」やPower Queryを使用してください。

期待通りに分割されない

原因:
区切り文字の指定ミス、または区切り文字が連続して存在する場合に、空の要素が意図せず生成されている可能性があります。

対処法:
1. 区切り文字を再確認する: 数式バーで、指定した区切り文字が正しいか、全角・半角の違いがないかを確認してください。特に、ダブルクォーテーションで囲まれているかを確認しましょう。
2. ignore_empty引数を活用する: 連続する区切り文字によって生じる空のセルを無視したい場合は、数式の4番目の引数にTRUEを指定してください。例: =TEXTSPLIT(A1,",",,TRUE)
3. 複数の区切り文字を確認する: 複数の区切り文字を指定する場合、配列定数 `{ “|”, “,” }` のように正しく記述されているか確認してください。

数式がエラーになる (#SPILL!エラーなど)

原因:
Excelのスピル機能(動的配列)に関連するエラーです。TEXTSPLIT関数は結果を複数のセルに展開(スピル)しますが、展開先のセルに既にデータが存在する場合などにエラーが発生します。

対処法:
1. 展開先のセルをクリアする: TEXTSPLIT関数を入力したセルの右側や下側に、結果を展開するための十分な空きセルがあることを確認してください。もし、展開先のセルにデータが存在する場合は、そのデータを削除するか、別の場所に数式を入力してください。
2. スピル範囲を確認する: エラーが発生しているセルを選択し、Excelの画面上部に表示される青い枠(スピル範囲)を確認してください。その範囲に他のデータがないか確認します。
3. pad_with引数を検討する: 意図せずスピル範囲が広がるのを防ぐために、pad_with引数で固定値を指定することも有効な場合があります。

数式が長すぎる、または複雑すぎる

原因:
非常に複雑な区切り文字の組み合わせや、多くの引数を指定している場合に、数式が読みにくくなることがあります。

対処法:
1. Power Queryの利用を検討する: 複雑なデータの前処理や、繰り返し行うデータ整形作業には、Power Queryがより適している場合があります。Power QueryはGUI操作でデータ変換を行えるため、数式が長くなりがちな場合に有効です。
2. 補助列の使用: 複雑な分割を段階的に行うために、一時的に補助列を使用することも有効です。例えば、まず大まかな区切り文字で分割し、その後、補助列でさらに詳細な分割を行うといった方法です。

TEXTSPLIT関数と他の関数・機能の比較

TEXTSPLIT関数は文字列操作において強力ですが、他のExcel機能と比較することで、その特性をより深く理解できます。

機能	TEXTSPLIT関数	区切り位置指定ウィザード	Power Query
主な用途	数式で動的に文字列を分割	固定的な区切り文字や固定幅で文字列を分割	複雑なデータ変換、ETL処理
操作方法	関数入力	ウィザード形式のGUI操作	GUI操作、M言語
動的対応	高い(元のデータ変更で自動更新)	低い(一度実行すると固定)	高い(クエリの再実行で更新)
複雑な区切り文字	対応可(配列指定)	限定的	対応可
複数行への展開	対応可	不可	対応可
Excelバージョン	2021, Microsoft 365	全バージョン	2016以降、Microsoft 365

TEXTSPLIT関数は、数式だけで完結させたい場合に最適です。一方、一度きりのデータ整形であれば「区切り位置指定ウィザード」が手軽です。大量のデータや、繰り返し行う複雑なデータ整形、異なるデータソースからの取り込みといった用途では、Power Queryが最も強力な選択肢となります。

まとめ

TEXTSPLIT関数を使いこなすことで、Excelでの文字列分割作業が劇的に効率化されます。区切り文字の指定方法や、空の要素の無視、複数行への展開といった機能を使うことで、様々なデータ形式に対応できます。

今後は、このTEXTSPLIT関数と他の文字列関数(LEFT、RIGHT、MID、FINDなど)や、配列関数(FILTER、SORTなど)を組み合わせることで、さらに高度なデータ処理が可能になるでしょう。まずは、手元のデータをTEXTSPLIT関数で分割する練習から始めてみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】