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

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で最新情報をお知らせ
記事がお役に立てたらシェアお願いします♪

読者登録お待ちしてます

今さらながらブログ村に参加しました。アラフィフ・40代・派遣社員・主婦・シンプルライフなど趣味の近い方と繋がりたいのが目的です。

読者登録しておくと、同じジャンルのブログの新規投稿を見逃さずに読めます。


きむおばブログ - にほんブログ村

にほんブログ村 主婦日記ブログへにほんブログ村 受験ブログ 中学受験終了組(本人・親)へにほんブログ村 就職バイトブログ 派遣社員へ