デスクの上のスマホや書類

お客様の情報を管理しているような場合、その情報の中には必ず「電話番号」があります。

この電話番号ですが、表現方法としては

  • ハイフン有り(例:03-1234-5678)
  • ハイフンなし(例:0312345678)

の2種類がありますが、電話番号をファイルなどに入力する際の運用が徹底されていない場合、

電話番号がハイフン有り表記とハイフン無し表記が混在している

という状態が発生します。あるお客様の情報ではハイフン有りだけど、あるお客様ではハイフン無し、といった具合です。電話番号を一覧で確認するとよくわかると思います。

ぶっちゃけ、電話番号にハイフンが付いていようが付いていまいが、番号自体が間違っていなければどちらでも良いのですが、管理上はどちらかに統一してあったほうが見やすくなります。

とはいえ、電話番号にハイフンを入れる、というのは、中々手間な部分があります。自分の住んでいる市区町村であれば先頭から何桁までが市外局番か分かりますが、それ以外となると、東京や大阪などの有名どころ以外は中々わからないものだからです。

ハイフン無しの電話番号をハイフン有り表記にする際の手間
  1. ハイフン無しの電話番号を見て、市外局番がどこまでかを確認する。
  2. 市外局番がどこまでなのか分からない場合、Googleなどで電話番号を入れて検索をし、市外局番が何桁目までなのかを確認する
  3. ハイフン有りの電話番号を管理表に入力する

このように、分からない場合は調べるという一手間が発生します。毎回これを行うのはめんどくさいですし、市外局番を全て覚えるというのも中々大変です。さらに、手元に既に持っている電話番号リストをハイフン有り表記に直すという事を行うような場合、上記のことを1つ1つやって置換処理をしていくのは正に時間の無駄です。

そこで今回は、ハイフン無しの電話番号を元にハイフン有りの電話番号を表示する方法をご紹介します。使うのはおなじみのエクセルです。一度エクセルで変換表を作ってしまえば、後は簡単にハイフン有りの電話番号を表示させる事ができますので、大量の電話番号のリストに対しての変換を一瞬で行うことができるようになります。

以下では、全国の電話番号に対応した電話番号のハイフン有りへの変換処理を作るサンプルをご紹介します。これらは、エクセルの

  • TEXT関数
  • IF関数
  • IFERROR関数
  • RIGHT関数
  • LEFT関数
  • MID関数
  • LEN関数
  • VLOOKUP関数

を使う事で実現する事ができます。

全国の市外局番・市内局番の一覧の入手

全国の電話番号に対応した変換を行わせるために、まずは全国の市外局番・市内局番の一覧を入手します。市外局番の一覧は総務省のホームページからファイルでダウンロードすることができます。

総務省HPの市外局番・市内局番の一覧

Word版とPDF版がありますので、Word版をダウンロードしておきます。

総務省の市外局番の一覧のダウンロードページ

ファイルの中身はこのようになっています。

市外局番の一覧(Word版)のファイルの中身

今回のサンプルで使うのは、このなかの市外局番市内局番の部分です。

市外局番は先頭に0が付きます。市内局番は「CDE」「DE」「E」というような表記になっていますが、それぞれ桁数を表しています。

市内局番の欄の「CDE」「DE」「E」等は、市内局番の桁数が、それぞれ「3桁」「2桁」「1桁」等を示すものです。

総務省のHPより引用

この一覧情報を元に、市外局番、市内局番、それ以外 に分割する処理をこれから作っていきます。

電話番号をハイフン無しからハイフン有りへ変換する際の考え方

まずは、どのような変換をすればハイフン有りの電話番号を表示させる事ができるようになるかを考えます。今回のサンプルでは、次のような考え方で処理を作ります。

電話番号をハイフン区切りへ変換する際の考え方
  1. 全国の市外局番一覧(市外局番・市内局番の一覧)を用意する。
  2. 電話番号(ハイフン無し)の先頭X文字に対して、全国の市外局番一覧を検索(VLOOKUP)して、該当する市外局番が存在するかをチェックする。存在する場合、市外局番と市内局番の桁数を取得する。
  3. 市外局番と市内局番の桁数を元にして、電話番号(ハイフン無し)をハイフン区切りに分割する。

※上記の2において存在しない場合、電話番号が間違っているか、全国の市外局番一覧が古いかのどちらかが考えられます。

全国の市外局番一覧(市外局番・市内局番の一覧)には、

  • 市外局番
  • 市内局番

の情報が載っています。そのため、

  • 市外局番の桁数
  • 市内局番の桁数

が分かります。固定電話の桁数は10桁ですので、

  • 1桁目 から「市外局番の桁数」文字=市外局番
  • 「市外局番の桁数」桁目+1 から「市内局番の桁数」文字=市外局番
  • 「市内局番の桁数」桁目+1 から 10桁目 =残りの番号

と判断できます。このような考え方により、電話番号(ハイフン無し)をハイフン区切りにする事ができます。

ポイントとしては、「全国の市外局番一覧(市外局番・市内局番の一覧)」を使用して市外局番の存在チェックをするという点です。市外局番を全て覚える事は現実的に無意味ですので、この一覧を元に存在をチェックできれば、毎回毎回「市外局番を調べる」という労力を減らす事ができます。

電話番号をハイフン区切りへ変換するサンプル

電話番号をハイフン区切りへ変換にはエクセル関数を組み合わせれば可能ですが、1セル内に複数の関数を入れ込んで数式を作ってしまうと、シンプルでもわかりにくくなります。そこで今回のサンプルは、使用する情報毎に列を分けて数式を表示し、最終的にはそれらを組み合わせてハイフン有りの電話番号を表示させるようなシートを作成しました。

完成形はこのようなイメージです。

電話番号をハイフン区切りにする完成形

市外局番・市内局番用のシートの生成

まず、市外局番・市内局番用のシートを作成します。先述の総務省HPの市外局番・市内局番の一覧からダウンロードしたWordファイルを開き、一覧部分をコピーし、シートのA~D列に貼り付けます。

市外局番・市内局番一覧をExcelにコピペ

貼り付け直後は文字のみになりますので、見やすいようにタイトル部分への色づけや枠線をつけます。

次に、変換用の情報をE~H列に数式を設定します。

市外局番・市内局番一覧への変換用情報の設定

まず、H列には「加入者番号の桁数」を指定します。加入者番号とは電話番号から市外局番と市内局番を取り除いた番号の事で、固定電話の場合は下4桁の部分が該当します。そのため、H列には「4」を固定で入力します。

次にG列に「市内局番の桁数」を表示する数式を入れます。

市内局番の桁数の数式

数式は G4セルの場合、=LEN(D4) です。LEN関数は文字の長さを返す関数ですので、ここではCDEの長さである3が結果として設定されます。

D列の市外局番について

CDEは3桁、DEは2桁、Eは1桁を表している

同様にして、F列には「市外局番の桁数」を表示する関数を入れます。

市外局番の桁数の数式

数式は F4セルの場合、=10-G4-H4 です。固定電話番号は全部で10桁なので、「10-市内局番の桁数-加入者番号の桁数」により桁数を算出する事ができます。

最後に、E列に「市外局番(先頭0埋め)」を表示する関数を入れます。

市外局番(0埋め)の桁数の数式

C列を見ると分かるのですが、総務省HPの市外局番・市内局番の一覧からダウンロードしたデータ内の市外局番には先頭に0が付いていません。そのため、C列をそのままでは利用できないため、E列にて先頭に0を付けるような処理を入れています。

数式は=RIGHT(TEXT(C4, "00000"), F4) としています。TEXT(C4, "00000")の部分ではセルに対してフォーマット変換を行っており、C4セルが22の場合は00022という文字が返ってきます。この文字に対してRIGHT関数にて右側3桁を取って022が最終的に算出されています。

市外局番(0埋め)

市外局番の先頭に0が付くので、="0"&C4 としても問題ありません。

以上で、市外局番・市内局番の変換用のシートが出来上がりました。

電話番号変換シートの作成

先ほどとは別のシートに、ハイフン無しの電話番号を入れるとハイフン有りを表示するシートを作ります。完成のイメージは次の通りです。

電話番号のハイフン区切り表示の完成イメージ

A列にハイフンを含めない電話番号を入れると、N列にハイフン区切りにした電話番号が表示されるようなシートです。該当が無い場合は「該当無し」と表示させます。固定電話の10桁以外に、携帯電話の11桁にも最終的には対応させています。

電話番号の桁数の算出

まず、B列に「電話番号の桁数表示用の列」を作成します。

電話番号の桁数を表示する数式

A列の電話番号に対してLEN関数を使用して桁数を表示させます。B2セルの場合、「=LEN(A2」です。この例だと電話番号が0312345678なので、10が返されています。

電話番号の先頭文字に対する市外局番の存在チェックの算出

C列からG列で「電話番号の先頭文字を比較する列」を設定します。

電話番号の先頭文字を比較する列の作成例

C列~F列では電話番号の先頭X文字が市外局番・市内局番一覧に存在するかをチェックし、G列ではどの市外局番を採用するかを判別しています。

C列~G列で行っている内容
  • C列:電話番号の先頭5桁が市外局番・市内局番一覧に存在するか
  • D列:電話番号の先頭4桁が市外局番・市内局番一覧に存在するか
  • E列:電話番号の先頭3桁が市外局番・市内局番一覧に存在するか
  • F列:電話番号の先頭2桁が市外局番・市内局番一覧に存在するか
  • G列:C~F列の中で、どの市外局番に該当するか(優先度はC>D>E>F)

なぜこんな事を行っているのかというと、市外局番というのは先頭文字が同じケースがあるためです。例えば静岡の市外局番で言うと

  1. 市外局番が3桁:「054」
  2. 市外局番が4桁:「0544」「0545」「0547」「0548」

といったように、どれも先頭が「054」から始まりますが、市外局番が3桁の場合と4桁の場合の2種類が存在するためです。電話番号が「0540123456」であれば「054」が市外局番ですが、「0544567890」であれば「0544」が市外局番であると判断する必要があります。

この判断をするために、電話番号の先頭5桁/4桁/3桁/2桁に対して市外局番・市内局番一覧に存在するかをチェックしています。市外局番が5桁というのは最近中々見かける事はありませんが、地方の一部ではまだありえるため、念のため考慮しています。

C列~F列では「電話番号の先頭X文字が市外局番・市内局番一覧に存在するか」をチェックしています。例えばC2セルの場合は、市外局番・市内局番一覧と電話番号の先頭5桁を比較することを行っていますが、このようになっています。

市外局番・市内局番一覧と電話番号の先頭5桁を比較する数式
電話番号の先頭5桁が市外局番・市内局番一覧に存在するかを確認するための数式(C2セル)

=IFERROR(VLOOKUP(LEFT($A2,5), 市外局番・市内局番!$E:$H,1,FALSE), "-")

関数が少し長いため数式を色づけすると、次のようになります。

=IFERROR(VLOOKUP(LEFT($A2,5), 市外局番・市内局番!$E:$H,1,FALSE), "-")

まず、青字の部分です。VLOOKUP関数で、「市外局番・市内局番」シートのE列~H列に対して、電話番号の先頭5桁( LEFT($A2,5) の部分)が存在するかをチェックし、存在してれば1列目(ここではE列)の値を返す、という事を行っています。

次に赤字の部分です。IFERROR関数で、青字の部分の結果が存在しない場合はハイフン(-)を表示するという事を行っています。

上記のC2セルで言うと、電話番号の先頭5文字(03123)が市外局番・市内局番一覧に存在するかをチェックしています。存在しないため、結果としてはハイフン(-)が表示されています。

D列からF列も同様の処理を行っています。違いは、LEFT関数で切り取る桁数です。

電話番号の先頭4桁が市外局番・市内局番一覧に存在するかを確認するための数式(D2セル)

=IFERROR(VLOOKUP(LEFT($A2,4), 市外局番・市内局番!$E:$H,1,FALSE), "-")

補足:VLOOKUPの第3引数がこの場合は1となっています。1を指定した場合、下記から分かるようにE列の「市外局番(先頭0埋め)」の値が返されます。

VLOOKUPの第3引数と対応表
電話番号の先頭3桁が市外局番・市内局番一覧に存在するかを確認するための数式(E2セル)

=IFERROR(VLOOKUP(LEFT($A2,3), 市外局番・市内局番!$E:$H,1,FALSE), "-")

電話番号の先頭2桁が市外局番・市内局番一覧に存在するかを確認するための数式(F2セル)

=IFERROR(VLOOKUP(LEFT($A2,2), 市外局番・市内局番!$E:$H,1,FALSE), "-")

最後にG列で、どの市外局番を採用するかを数式で算出しています。

どの市外局番を採用するかを数式

IF関数を使って、C列→D列→E列→F列の順にチェックをしていき、ハイフン(-)ではない市外局番を表示させています。C列~F列まで全てハイフン(-)の場合、該当無しとして空文字を表示しています。

先述のとおり、市外局番は先頭が同じでも3桁・4桁と分かれるケースがあるため、桁数が多いほうから優先にチェックを行う事で、意図する市外局番が取得できるようになります。

市外局番・市内局番・加入者番号の桁数の算出

G列には市外局番が算出されましたので、これを元にH列~J列ではそれぞれ市外局番・市内局番・加入者番号の桁数を表示させます。市外局番・市内局番・加入者番号の桁数は「市外局番・市内局番」シートに持っていますので、VLOOKUP関数を用いて桁数を引っ張ってきます。

市外局番・市内局番・加入者番号の桁数の算出
市外局番・市内局番・加入者番号の桁数を算出する数式(H2~J2セル)

市外局番の桁数
=IFERROR(VLOOKUP($G2,市外局番・市内局番!$E:$H, 2, FALSE), "")

市内局番の桁数
=IFERROR(VLOOKUP($G2,市外局番・市内局番!$E:$H, 3, FALSE), "")

加入者番号の桁数
=IFERROR(VLOOKUP($G2,市外局番・市内局番!$E:$H, 4, FALSE), "")

補足:VLOOKUP関数の第3引数への設定値と、それに対応する取得される値の関連について

VLOOKUPの第3引数と対応表

これにより市外局番・市内局番・加入者番号のそれぞれの桁数が算出できました。あと一息で完成です。

市外局番・市内局番・加入者番号の算出

市外局番・市内局番・加入者番号のそれぞれの桁数が算出できたので、K列~N列ではその情報を元に番号を算出しています。

電話番号のハイフン区切り
市外局番・市内局番・加入者番号を算出する数式(K2~N2セル)

市外局番
=G2

市内局番
=IF(K2="", "", MID(A2, H2+1, B2-H2-J2))

加入者番号
=IF(K2="", "", RIGHT(A2, I2))

電話番号 (ハイフンあり)
=IF(B2=11, LEFT(A2, 3)&"-"&MID(A2, 4, 4)&"-"&RIGHT(A2, 4), IF(K2="", "該当なし", K2&"-"&L2&"-"&M2))

市内局番・市内局番ではIF関数を用いていますが、これは該当する市外局番がない場合をK2セルの値を元に判定しているためです。

電話番号 (ハイフンあり)でもIF関数を用いていますが、B2セル(電話番号の桁数)が10桁(固定電話)の場合か11桁(携帯電話番号の場合か)によって処理を分けているためです。

以上でハイフン無しの電話番号をハイフン区切りにするシートが完成しました。

フリーダイヤルへの対応

上記は固定電話の場合ですが、このままではフリーダイヤル(0120始まり)には対応していません。

対応するには、「市外局番・市内局番」シートに変換情報を追加してあげればよいです。

変換表にフリーダイヤル変換用の行を追加

このようにすることで、フリーダイヤルもハイフン区切りができるようになります。

フリーダイヤルの変換サンプル

変換表があることで、数式を変えることなく対応できるのも便利な点ですね。

まとめ

エクセル関数を使って電話番号をハイフン区切りにする方法をご紹介しました。エクセル関数は便利なため色々な用途で使えますが、まずはやりたい事をどうやれば実現できるかを、手順を細かく区切って考えてみて見ましょう。

サンプルで作ったExcelはこちらからダウンロード可能です。(パスワードはkeizokuma)

記事やサンプルが役に立った場合、是非SNSでシェアをお願いします。

Twitterでフォローしよう

おすすめの記事