事務ツール

Excel で電話番号にハイフンを入れる式と、固定電話で式が使えない理由

名簿の電話番号が「09012345678」「0312345678」のようにハイフンなしで入っていると、読みにくく、打ち間違いにも気づきにくくなります。携帯電話の番号は Excel の式でそろえられますが、固定電話は式だけではうまくいきません。

携帯電話の番号（11 桁）にハイフンを入れる式

090・080・070 で始まる携帯電話や、050 の IP 電話の番号は、どれも「3 桁-4 桁-4 桁」です。A2 に「09012345678」が文字として入っているなら、次の式で区切れます。

=LEFT(A2,3)&"-"&MID(A2,4,4)&"-"&RIGHT(A2,4)

CSV を Excel で開いて先頭の 0 が消え、「9012345678」という数値になっているときは、TEXT 関数を使うと 0 を戻しながら区切れます。

=TEXT(A2,"000-0000-0000")

どちらも、結果をコピーして「値の貼り付け」で元の列に上書きすれば、式ではなく文字として残せます。先頭の 0 が消える理由と防ぎ方は先頭の 0 が消える理由と防ぎ方で説明しています。

固定電話は市外局番の長さがばらばら

固定電話の番号は 0 を含めて 10 桁で、「市外局番-市内局番-加入者番号」に分かれます。加入者番号はいつも 4 桁ですが、市外局番は 2〜5 桁と長さが決まっていません。総務省の電気通信番号指定状況のデータで数えると、市外局番は次のとおりです。

市外局番の桁数数例区切り
2 桁303・04・0603-1234-5678
3 桁48011・045・092045-123-4567
4 桁2960422・0466・01540422-12-3456
5 桁4001267・0499201267-1-2345

そのため「頭の 2 桁で区切る」「3 桁で区切る」といった 1 つの式では、多くの番号を切り間違えます。

番号の頭だけでも区切りは決まらない

「042 で始まるなら 3 桁」のように、頭の数字で市外局番の長さを決める式を作ろうとしても、うまくいきません。同じ頭で始まる番号でも、続く数字によって市外局番が違うことがあるからです。

市外局番と市内局番の組み合わせは、番号の先頭 6 桁ごとに総務省が決めて公表しています。正しく区切るには、この一覧と照らし合わせる必要があります。Excel でやるなら、一覧を別のシートに入れて VLOOKUP などで引くことになりますが、市外局番だけで 387、先頭 6 桁の組み合わせは 4 万件以上あります。

確実にそろえるには

このサイトの電話番号のハイフンを一括でそろえるは、総務省のデータから作った市外局番の一覧をページに組み込んであり、固定電話の番号を正しく区切ります。携帯電話・IP 電話・0120・0800・0570 の番号もそれぞれの形で区切り、「+81」で始まる番号は 0 に直します。

読み込んだ名簿はどこにも送信しません。ページは外部への通信を禁止する設定のまま動きます。

区切らずに保存するときの注意

ハイフンのない電話番号を CSV に保存し、その CSV を Excel でダブルクリックして開くと、また先頭の 0 が消えます。ほかのシステムに読み込ませるためにハイフンなしにするときは、Excel で開かずに渡すか、先頭の 0 が消える理由と防ぎ方の方法で読み込んでください。

参考にした資料