【Excel】TEXTSPLIT関数で区切り文字のある文字列を分割する方法

文字列データの中から特定の情報を抽出し、分割する作業に手間を感じていませんか。

ExcelのTEXTSPLIT関数を使えば、区切り文字ごとに文字列を複数のセルに簡単に分けられます。

この記事では、TEXTSPLIT関数の基本的な使い方から、複数の区切り文字の指定、結果の表示方向の変更までを詳しく解説します。

文字列の整理作業を効率化し、データ分析の準備をスムーズに進める手助けとなるでしょう。

TEXTSPLIT関数の概要と利用条件

TEXTSPLIT関数は、指定した区切り文字に基づいて文字列を複数の部分に分割し、それぞれを隣接するセルに出力する動的配列関数です。

例えば、「りんご,みかん,バナナ」のようなCSV形式のデータを「りんご」「みかん」「バナナ」と別々のセルに展開できます。

住所録の「都道府県市町村」と「番地以降」を分けるなど、多様なビジネスシーンでデータ整形に役立ちます。

この関数は、Excel for Microsoft 365およびExcel for the webでのみ利用できます。

Excel 2019やExcel 2021などの永続ライセンス版では使用できないため注意が必要です。

TEXTSPLIT関数の書式

TEXTSPLIT関数の書式は次の通りです。

=TEXTSPLIT(テキスト, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

• テキスト: 分割したい元の文字列を指定します。
• col_delimiter: 列方向に分割するための区切り文字を指定します。
• [row_delimiter]: オプションです。行方向に分割するための区切り文字を指定します。
• [ignore_empty]: オプションです。TRUEを指定すると、空のセルが結果に含まれません。デフォルトはFALSEです。
• [match_mode]: オプションです。0は完全一致、1は大文字小文字を区別しない一致です。デフォルトは0です。
• [pad_with]: オプションです。埋める値を指定します。結果が不完全な場合に空きセルを埋める値です。

TEXTSPLIT関数の具体的な操作手順

TEXTSPLIT関数のさまざまな使い方をステップバイステップで解説します。

1. 基本的な文字列の列方向への分割

1. 元データの準備
   ExcelシートのA1セルに「りんご,みかん,バナナ」と入力します。
2. 数式の入力
   B1セルに「=TEXTSPLIT(A1,",")」と入力し、Enterキーを押します。
3. 結果の確認
   B1セルには「りんご」、C1セルには「みかん」、D1セルには「バナナ」が自動的に表示されます。

2. 複数の区切り文字を指定して分割する手順

異なる区切り文字が混在する文字列を一度に分割できます。

1. 元データの準備
   ExcelシートのA1セルに「商品A;商品B,商品C」と入力します。
2. 数式の入力
   B1セルに「=TEXTSPLIT(A1,{";",","})」と入力し、Enterキーを押します。
3. 結果の確認
   B1セルに「商品A」、C1セルに「商品B」、D1セルに「商品C」が表示されます。

3. 文字列を行方向に分割する手順

分割結果を縦方向に展開したい場合に利用します。

1. 元データの準備
   ExcelシートのA1セルに「りんご,みかん,バナナ」と入力します。
2. 数式の入力
   B1セルに「=TEXTSPLIT(A1,,",")」と入力し、Enterキーを押します。
3. 結果の確認
   B1セルに「りんご」、B2セルに「みかん」、B3セルに「バナナ」が表示されます。col_delimiterを省略しrow_delimiterにコンマを指定しています。

4. 空のセルを無視して分割する手順

区切り文字が連続している場合に、意図しない空のセルが生成されるのを防ぎます。

1. 元データの準備
   ExcelシートのA1セルに「りんご,,バナナ」と入力します。
2. 数式の入力
   B1セルに「=TEXTSPLIT(A1,",",,TRUE)」と入力し、Enterキーを押します。
3. 結果の確認
   B1セルには「りんご」、C1セルには「バナナ」が表示されます。空のセルが無視され、C1に「バナナ」が配置されます。

TEXTSPLIT関数利用時の注意点と制限事項

TEXTSPLIT関数を使用する際に遭遇しやすい問題とその対処法を解説します。

TEXTSPLIT関数が#NAME?エラーになる

原因: TEXTSPLIT関数は、Excel for Microsoft 365でのみ利用できる新しい動的配列関数です。

Excel 2019やExcel 2021などの永続ライセンス版ではこの関数が存在しないため、#NAME?エラーが表示されます。

対処法: Excel for Microsoft 365の環境で利用するか、旧バージョンで利用可能な代替手段を検討してください。

代替手段としては、区切り位置指定ウィザード、LEFT/RIGHT/FIND関数を組み合わせた数式、またはPower Queryを利用する方法があります。

分割結果が#SPILL!エラーになる

原因: TEXTSPLIT関数は、結果をスピル範囲と呼ばれる複数のセルに自動的に展開します。

しかし、結果が出力される予定のセル範囲に、すでにデータや別の数式が入力されている場合、#SPILL!エラーが発生します。

これは、関数が結果を出力するための十分なスペースがないことを示しています。

対処法: #SPILL!エラーが表示された場合は、スピル範囲として指定されているセル範囲(数式を入力したセルから右方向または下方向に展開される範囲)に存在するデータを削除するか、数式を別の空いているセルに入力し直してください。

意図しない空のセルが結果に含まれる

原因: 区切り文字が連続している場合、TEXTSPLIT関数はデフォルトでその間の空の文字列も一つの要素として扱います。

例えば、「A,,B」をコンマで分割すると、「A」「空の文字列」「B」の3つの要素が生成されます。

対処法: 空のセルを結果から除外したい場合は、TEXTSPLIT関数の第4引数であるignore_emptyにTRUEを指定してください。

例えば、「=TEXTSPLIT(A1,",",,TRUE)」のように設定します。

区切り文字の指定ミスで正しく分割できない

原因: 区切り文字が正しく認識されていない場合があります。

全角と半角の区別、スペースの有無、あるいは複数の異なる区切り文字が存在するにもかかわらず、1つしか指定していないなどが考えられます。

対処法: 元の文字列データを確認し、実際に使われている区切り文字と数式で指定している区切り文字が完全に一致しているか確認してください。

複数の区切り文字が存在する場合は、波かっこ{}を使って配列形式で全て指定する必要があります。

TEXTSPLIT関数と他の文字列分割方法の比較

TEXTSPLIT関数以外にもExcelには文字列を分割する方法があります。

それぞれの特徴を比較し、状況に応じた使い分けを検討できます。

項目	TEXTSPLIT関数	区切り位置指定ウィザード	Power Query	VBA (Split関数)
対応バージョン	Excel for Microsoft 365	全てのExcelバージョン	Excel 2010以降(アドイン/標準機能)	全てのExcelバージョン
操作の手軽さ	数式入力で完結、動的に更新	ウィザード形式、手動操作が必要	クエリ作成、データ更新は手動または自動	VBAコード作成、マクロ実行が必要
動的な更新	元データ変更で即時反映	再実行が必要	更新ボタンで反映、自動更新設定可能	コード再実行が必要
複雑な条件対応	複数の区切り文字、空のセル無視など柔軟	固定長/区切り文字、比較的単純	高度な変換、柔軟なデータ操作	VBAコード次第で高度な処理が可能
複数区切り文字	可能(配列で指定)	1つのみ(または固定長)	可能(M言語で柔軟に指定)	可能(Split関数は1つ、VBAで複数処理)

まとめ

TEXTSPLIT関数を活用することで、区切り文字を含む文字列データを効率的に分割できます。

複数の区切り文字の指定、空のセルの無視、行方向や列方向への展開など、柔軟な設定が可能です。

この関数は、データ整形の手間を大幅に削減し、データ分析の精度向上に貢献します。

TEXTSPLIT関数を他の動的配列関数やExcelの機能と組み合わせることで、さらに高度なデータ処理も実現できます。

📊

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

🏆

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