今日、Excelを使っていて、とっても小さいけれど奥深くて解決できないつまづきに遭いました。

それは、ユーザ入力で16桁の番号を4桁ずつ自動でハイフン区切りしたいけれど、16桁目が0になってしまう問題です。

すぐに解決できると思っていたのですが、Excelの仕様で根深い問題だったようです。

世の中には16桁の数字の羅列が結構ある

16桁の数字というと、何を思い浮かべますか？

そうそう、クレジットカードの番号も16桁ですよね。
あとは、e-taxの利用者識別番号とか、Tポイントの番号とか、Nintendo Switchのダウンロード番号とか…世の中は、16桁の数字の組み合わせで溢れている。

Excelで、クレジットカード番号の数字16桁を入力すると、自動で4つずつハイフンで区切られるようにしたかったのです。1分で終わる作業と思って甘く見ていました。

16桁目以降が0になるのはエクセルの仕様です

ユーザー定義で、「####”-“####”-“####”-“####」と設定すれば、解決すると思っていたのですが、この方法では、16桁目以降は全てゼロ表示になってしまいます。

ググってみると、マイクロソフト公式サポートの回答に辿り着きました。

Microsoft Excel では、セルに 15 桁を超える数字を入力すると、15 桁目より下位のすべての桁がゼロに変更されます。たとえば、クレジット カードの ID 番号を次の形式で入力した場合に、この問題が発生します。

＞＞＞Excel のセルに桁数の多い数字を入力すると最後の桁がゼロに変更される

• セルの書式を「文字列」にすると、16桁表示されるけどハイフンが自動で付かない。
• セルの書式を「数値」にすると、指数表示されてしまう。
• セルの書式を「ユーザー定義」にしても、桁溢れで16桁目が0になってしまう。

いろいろと試してみましたが、ユーザー定義では永遠に解決できないエクセルの仕様だったのです。

16桁数字にハイフンを自動付与する解決策はMID関数しかないのか

結局のところ、ハイフンを入力する時間を節約したいのだけれどユーザー定義は使えなかったため、MID関数でベタに解決するしか思いつきませんでした。

納得いかないんですけどね…

もっとスマートで上手い方法が思いついた方、コソッと教えてください。