エクセルで電話番号にハイフンを入れるには？市外局番一覧から自動区切り

顧客情報などをエクセルで管理しているような場合、電話番号をハイフンありなしのどちらで管理しているでしょうか？

電話番号をハイフンなしで管理しているような場合、「電話番号にはハイフンを入れない」という簡単な運用ルールを統一できるので便利な反面、後からハイフンを入れることをしたい場合に手間が出てきます。

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

そこで今回は、エクセルを用いて電話番号にハイフンを自動で入れる方法をご紹介します。

この記事で紹介する方法を利用することで、地域によって異なる市外局番の桁数を自動でハイフン区切りできるようになります。全国どの電話番号にも対応可能なので、電話番号のリストが大量にある場合は特に役立つことでしょう。

電話番号にハイフンを入れるエクセルサンプル

今すぐにでも電話番号をハイフン区切りにしたい場合、以下ボタンからサンプルエクセルをダウンロードして使ってみてください。

ボタンをクリックするとエクセルファイルのファイルダウンロードページに遷移します。

以下では、サンプルエクセルでどのような手順で電話番号にハイフンを入れるかを解説していきます。（エクセル内にも補足用シートを入れています）

エクセルで電話番号にハイフを入れる際の考え方

サンプルエクセルでは、次の考え方でハイフンなしの電話番号に対してハイフンを入れる処理を実現しています。

全国の市外局番一覧（市外局番・市内局番の一覧）には、

• 市外局番
• 市内局番

の情報が載っていますので、この情報からそれぞれの桁数を算出できます。

固定電話の桁数は10桁なので、

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

と判断できます。

ポイントは、「全国の市外局番一覧」を使用して市外局番の存在チェックをするという点です。この一覧のおかげで全国の電話番号に対してハイフンを入れることができるようになります。

電話番号にハイフンを入れるサンプルエクセルの解説

実際にサンプルエクセル内でどのようにして電話番号にハイフンを入れる処理が入っているのか、1つ1つ解説をしていきます。

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

全国の電話番号に対応した変換を行わせるために、まず全国の市外局番・市内局番一覧を総務省のホームページからダウンロードします。

総務省HPのダウンロードページへ

Word版とPDF版が用意されていますが、ここではWord版をダウンロードします。

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

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

市外局番の列は実際の市外局番が数字で表現されていますが、先頭に0が付いていません。市内局番は「CDE」「DE」「E」というような表記になっており、それぞれ桁数を表しています。

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

総務省のHPより引用

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

先述のダウンロードしたWordファイルを開いたのち、一覧の部分をコピーしてエクセル内の「市外局番・市内局番」シートのA～D列に貼り付けます。

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

次に、ハイフンを入れるための桁数算出用数式をE～H列に設定します。

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

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

G4セルの場合、数式は「=LEN(D4)」となります。上記例ではＤ４セルの値「CDE」の長さである3が結果として設定されます。

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

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

最後に、E列に「先頭を0埋めした市外局番」を算出するための数式を入れます。

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

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

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

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

A列にハイフンが入っていない電話番号を入れると、N列にハイフン区切りされた電話番号が表示されます。（該当が無い場合は「該当無し」と表示）。固定電話の場合は全体で10桁ですが、携帯電話の場合は全体で11桁になりますので、エクセルサンプルでは最終的にどちらにも対応できるようにしていきます。

ハイフンなし電話番号の桁数算出用列を設定

まず、B列に「ハイフンなし電話番号の桁数」を数式で作成します。

A列の電話番号に対してLEN関数を使用して桁数を表示させます。B2セルの場合、数式は「=LEN(A2」となります。

電話番号の先頭文字に対する市外局番の存在チェック用列の設定

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

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

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

例えば静岡の市外局番で言うと、

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桁を比較することを行っており、次のような数式になっています。

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

=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関数で切り取る桁数です。

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

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

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

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

これにより市外局番・市内局番・加入者番号のそれぞれの桁数が算出できましたので、電話番号にハイフンを入れるまであと一歩で完成の状態となりました。

電話番号から市外局番・市内局番・加入者番号の算出

K列～N列では桁数情報を元に電話番号を区切り、それぞれの番号を算出しています。

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

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

以上でハイフン無しの電話番号に対してハイフンを入れるためのシートが完成しました。

フリーダイヤルへの対応

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

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

このようにすることで、フリーダイヤルに対してもハイフンを入れることができるようになります。

変換表があることで、数式を変えることなくハイフンを入れることができるのは便利な点ですね。

まとめ

エクセル関数を使って電話番号にハイフンを自動的に入れる方法をご紹介しました。

サンプルエクセルを使えばすぐにハイフン区切りはできますが、それだけではなく、やりたい事をどうやれば実現できるかを、手順を細かく区切って考える際の参考にもしていただけると幸いです。

また、この記事に書いたようなちょっとした困ったことでも、自分に経験や知識が足りないために対応できなかったり、時間をかけたわりに全然進まなかったりと、思うようにいかない事ってありますよね。そんなときは専門家に相談したり解決してもらったりする方法もありますので、一人で悩まず対処してみましょう。