きむおばプロフィール画像

きむおばです。関東在住の40代の主婦です。 契約社員・派遣・フリーランスの3足のわらじ中。フリーランスでは、ファイナンシャルプランナーとしてライターやセミナー講師をしたり、サイト制作やプログラムを作ったりしています。
お金・子育て・趣味のこと…何でも気ままに書く雑記ブログです。
目標は、ライター・サイト制作・広告他の収入バランスを整えること。ブログ収入UPに今年も力を入れます。

Menu
主婦の働き方・稼ぎ方・生き方 

Excelユーザー入力でハイフン自動付与したかったけれど16桁目が0になってしまう問題の解決方法は?

Excelの活用でもっと事務処理を時短化

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

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

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

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

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

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

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

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

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

A列 文字列
B列 数値
C列 ユーザー定義(####”-“####”-“####”-“####)

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

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

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

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

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

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

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

結局のところ、ハイフンを入力する時間を節約したいのだけれどユーザー定義は使えなかったため、MID関数でベタに解決するしか思いつきませんでした。
Excel のセルに桁数の多い数字を入力すると最後の桁がゼロに変更される解決方法

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

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

Excelの活用でもっと事務処理を時短化
Twitterで最新情報をお知らせ
記事がお役に立てたらシェアお願いします♪