TEXTSPLIT:区切り文字でセルの文字列を一括分割(スピル展開)
💡 やりたいこと
「東京,大阪,名古屋」のようにカンマ区切りで1つのセルに入っている文字列を、別々のセルに一気に分割して展開したい。
新関数での解決法:TEXTSPLIT
Excel 2024 / Microsoft 365 などの対応環境で使える関数で、指定した区切り文字で文字列を分割し、スピルで横方向(または縦方向)に自動展開します。Excel 2021以前や更新されていない職場PCでは使えない場合があります。
=TEXTSPLIT(テキスト, 列の区切り, [行の区切り])
=TEXTSPLIT(A1, ",")
意味:A1の中身を「,」で分割し、それぞれ別のセルに横方向へ展開する。
🔥 行方向と列方向の同時分割
第2引数で列の区切り(カンマ)、第3引数で行の区切り(セミコロン)を指定すると、二次元的に分割が可能。CSVデータの分解に威力を発揮します。
=TEXTSPLIT(A1, ",", ";")第2引数で列の区切り(カンマ)、第3引数で行の区切り(セミコロン)を指定すると、二次元的に分割が可能。CSVデータの分解に威力を発揮します。
よく使う書き方
縦方向に分割する
=TEXTSPLIT(A1,,",")
第2引数を空にして、第3引数(行の区切り)に指定すると、下方向へ展開します。
複数の区切り文字をまとめて指定する
=TEXTSPLIT(A1,{",","、"})
半角カンマと読点が混ざった文字列でも、まとめて分割できます。
連続した区切りで空欄を作らない
=TEXTSPLIT(A1,",",,TRUE)
第4引数を TRUE にすると、「東京,,大阪」のような空の要素を詰めて展開します。
つまずきやすい点
- 結果は文字列です。「10,20,30」を分割して計算に使うときは、
=VALUE(TEXTSPLIT(A1,","))のように数値へ変換します。 - 展開先にデータがあると
#SPILL!になります。展開する範囲を空けてください。 - 行ごとに列数がそろわないと、足りない部分は
#N/Aになります。第6引数で埋める値を指定できます(例:=TEXTSPLIT(A1,",",";",,,""))。
旧バージョンでの代替法
TEXTSPLITが使えない環境では、MID + FIND + ROW + INDIRECT の非常に長い配列数式を行数分コピーするか、「データ」タブの「区切り位置」機能(手動操作)を使って対処していました。
手作業を自動化できない上、数式も極めて複雑なため、パワークエリでの分割が実務では推奨されていました。