カンマ区切りの文字列を縦に分割するには、TEXTSPLITの第3引数「行区切り」にカンマを指定します。=TEXTSPLIT(A1,,",") のように、第2引数を空けるためカンマが2つ続くのがポイントです。
TEXTSPLITでカンマ区切りを縦に並べる
A1に「A001,A002,A003」と入力し、B3へ次の式を入力します。B3:B5にA001、A002、A003が1件ずつ表示されます。
=TEXTSPLIT(A1,,",")TEXTSPLITはMicrosoft 365・Excel 2024に対応します。Excel 2021・2019・2016向けの方法は後半で紹介します。
縦と横の違いは区切り文字を入れる位置
| 並べ方 | 数式 |
|---|---|
| 縦に並べる | =TEXTSPLIT(A1,,",") |
| 横に並べる | =TEXTSPLIT(A1,",") |
| カンマで列、セミコロンで行に分ける | =TEXTSPLIT(A1,",",";") |
「A001,東京;A002,大阪」を最後の式で分割すると、2行×2列の表になります。結果が広がる先に元データや別の数式を置かないでください。
連続するカンマや余分な空白への対処
A1が「A001,,A003」のように途中の項目が空の場合、その空項目を飛ばすには第4引数をTRUEにします。
=TEXTSPLIT(A1,,",",TRUE)項目の位置に意味があるデータでは、空項目を飛ばすと対応がずれるため、省略またはFALSEにしてください。
カンマの後ろの半角スペースも除きたい場合はTRIMで囲めます。ただし、項目内の連続する半角スペースも1個になります。
=TRIM(TEXTSPLIT(A1,,",",TRUE))全角の「，」は半角の「,」と別の文字です。元データの区切りに合わせて指定します。セル内改行で縦に分けるなら、行区切りをCHAR(10)にします。
2番目の項目だけ欲しい場合
縦に分割した結果の2行目をINDEXで選びます。
=INDEX(TEXTSPLIT(A1,,","),2)元データに2項目以上ある場合の式です。空項目を含めて2番目なのか、空項目を飛ばした後の2番目なのかを決めてから、TEXTSPLITの第4引数を設定します。
Excel 2021・2019・2016では補助的な分割式を使う
分割前の文字列全体が100文字未満の短いコード一覧などなら、次の式をB3に入力し、下へコピーする方法があります。
=TRIM(MID(SUBSTITUTE($A$1,",",REPT(" ",100)),ROW(A1)*100-99,100))カンマを100個の半角スペースに置き換え、MIDで100文字ずつ切り出し、TRIMで空白を整えます。ROW(A1)は1、下へコピーするとROW(A2)で2となり、次の項目を取り出します。
長い文章、空白自体を保持したいデータ、大量の項目にはこの方法は向きません。データの構造に応じてPower Queryなどの分割機能も検討してください。
#SPILL!・#NAME?・分割できないとき
| 症状 | 対処 |
|---|---|
| #SPILL! | 結果の下にあるデータや結合セルを確認する |
| #NAME? | TEXTSPLIT対応版か確認する |
| 1つの項目のまま | 半角・全角など、実際の区切り文字を確認する |
| 横に広がる | 行区切りではなく列区切りに指定していないか確認する |
スピルする数式は結果先の全セルへコピーしません。先頭セルに1つだけ入力します。また、CSVの引用符内に含まれるカンマまで正しく解釈したい場合は、TEXTSPLITで単純分割せず、［データ］のテキスト/CSV取り込み機能を使ってください。
特定の区切りの後ろだけが必要なら特定文字より後ろを抽出する方法、抽出済みの一覧を横へ並べたいならFILTERとTRANSPOSEの使い方が参考になります。
仕様の確認：Microsoft公式・TEXTSPLIT関数
コメント