はじめに

現役の高校教員（数学）です。エンジニアではありません。

前回まで、出席管理を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つ捨てて、既存ファイルに乗り換えた話を書く予定です。