はじめに
現役の高校教員（数学）です。エンジニアではありません。
前回まで、出席管理をExcelで作った話を書きました。今回はその中で使った、Excelの数式だけでカンマ区切り文字列を分割するテクニックの話です。
やりたいことはこれだけです。
入力欄に 110,237,605 と打つ
↓
別々のセルに 110 / 237 / 605 と展開したい
Excelには TEXTSPLIT という関数がありますが、新しめのバージョンにしかありません。学校のPCは環境がバラバラで、いつどのバージョンに当たるか分かりません。VBAで書く手もありますが、そこは既に別のマクロを使っていて、これ以上マクロを増やしたくありませんでした。
そこで、古いExcelでも確実に動く数式でやりました。
使った式
これです。
=IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE($D6&",", ",", REPT(" ",100)),
COLUMN(A1)*100-99, 100)))),
"",
VALUE(TRIM(MID(SUBSTITUTE($D6&",", ",", REPT(" ",100)),
COLUMN(A1)*100-99, 100))))
初見では何をしているのか分かりませんでした。分解すると単純です。
1. カンマを「大量の空白」に置き換える
核心はここです。
SUBSTITUTE($D6 & ",", ",", REPT(" ", 100))
REPT(" ", 100) は半角スペース100個を作ります。それでカンマを置換します。
入力が 110,237,605 なら、こうなります。
110[スペース100個]237[スペース100個]605[スペース100個]
各要素の間が、必ずぴったり100文字空く状態になりました。ここがすべてです。
区切り位置を探す必要がなくなり、位置が計算で決まるようになります。
2. 100文字ずつ切り出す
間隔が固定なら、あとは等間隔に切るだけです。
MID(文字列, COLUMN(A1)*100-99, 100)
COLUMN(A1) は 1 を返します。右にコピーすると COLUMN(B1) → 2、COLUMN(C1) → 3 と自動で増えます。
| n | 切り出す開始位置 | 取れるもの |
|---|---|---|
| 1 | 1文字目から100文字 |
110 ＋ 空白 |
| 2 | 101文字目から100文字 | 空白 ＋ 237 ＋ 空白 |
| 3 | 201文字目から100文字 | 空白 ＋ 605 ＋ 空白 |
切り出した結果は前後が空白まみれですが、その中に目的の値が必ず1つだけ入っています。
COLUMN() を使うのは、右方向にコピーするだけで連番になるからです。ここに 1, 2, 3 と直接書いてしまうと、コピーのたびに手で直すことになります。
3. 空白を落として数値にする
VALUE(TRIM( ... ))
TRIM で前後の空白を除去し、VALUE で数値化します。
4. 要素が足りないときの処理
110,237 のように2つしか入力されていない場合、3つ目を切り出すと空文字になり、VALUE("") が #VALUE! エラーになります。
そこで全体を IF(ISERROR(...), "", ...) で包みます。
=IF(ISERROR(VALUE(TRIM(MID(...)))), "", VALUE(TRIM(MID(...))))
これで、入力された数だけ展開され、余った枠は空欄になります。
同じ式を2回書くのが冗長ですが、IFERROR が使えない古い環境も想定してこうしました。IFERROR が使えるなら、こう書けます。
=IFERROR(VALUE(TRIM(MID(SUBSTITUTE($D6&",", ",", REPT(" ",100)),
COLUMN(A1)*100-99, 100))), "")
5. なぜ「100」なのか
REPT(" ", 100) の 100 は、1要素の最大文字数より十分大きければ何でもいい数字です。
私の場合、入力するのは3桁の番号なので、100は過剰なくらい余裕があります。ただし注意点が1つ。
区切り幅より長い要素が来ると壊れます。
例えば幅を 10 にして、ABCDEFGHIJKL（12文字）が来ると、切り出し窓に収まらず値が欠けます。扱うデータの最大長を見てから幅を決める必要があります。
私は「3桁の番号しか来ない」と分かっていたので、100で固定しました。ここは自分の入力仕様が決まっているからこそ使える手です。
6. なぜ末尾にカンマを足すのか
SUBSTITUTE($D6 & "," , ...)
$D6 & "," と、入力の末尾にカンマを1つ足しています。
これが無いと、最後の要素の後ろにスペースが付かず、MID の切り出し窓が最後だけ短くなって値が欠けることがあります。末尾にカンマを足しておくと、全要素が「値＋スペース100個」の同じ形になるので、例外がなくなります。
地味ですが、これを忘れると「最後の1件だけ取れない」という嫌なバグになります。
7. Claude Codeに何を聞いたか
この式は自分で思いついたものではありません。聞き方はこうでした。
「Excelでカンマ区切りの入力を分割したい。ただし
TEXTSPLITは使えない前提で、VBAも増やしたくない」
制約を先に全部言うのがコツだと思っています。制約を言わないと、たいてい一番新しくて楽な方法を教えてくれるからです。それが使えない環境にいるなら、先に言うしかありません。
そして返ってきた式について、「なぜ REPT で空白100個なのか」を追加で聞きました。**動く式をもらうだけでは、幅を変えたいときに壊せません。**理屈まで聞くと、自分の環境に合わせて調整できるようになります。
8. ハマったポイント
| ハマり | 対処 |
|---|---|
TEXTSPLIT が無い環境がある |
SUBSTITUTE+REPT+MID+TRIM で代替 |
| 最後の要素だけ取れない | 元の文字列の末尾にカンマを1つ足す |
入力が少ないと #VALUE!
|
IFERROR / IF(ISERROR(...)) で空欄に |
| 右にコピーしても連番にならない | 数字を直書きせず COLUMN(A1) を使う |
| 長いデータで値が欠ける |
REPT の幅を最大文字数より十分大きく |
| 全角カンマで入力される | 事前に SUBSTITUTE(D6,"，",",") で正規化する |
最後の全角カンマは、実運用で必ず起きます。日本語入力のまま打つと全角になるからです。ここは正規化を1枚かませておくと事故が減ります。
おわりに
やっていることは「区切り文字を固定幅の空白に変えて、等間隔で切る」だけです。理屈が分かれば単純ですが、自力では絶対に思いつきませんでした。
Excelの数式は、関数の名前を知らないと検索すらできないのがつらいところです。「カンマで分けたい」という日本語から REPT にたどり着くのは、非エンジニアにはまず無理でした。そこを日本語のまま相談できるのが、AIを使う一番の恩恵かもしれません。
次は、自作したExcelシステムを3つ捨てて、既存ファイルに乗り換えた話を書く予定です。