Excel約6分で読めます

Excelで電話番号の市外局番を分ける|桁数が違う番号

Excelで電話番号の市外局番を分けるを表したイメージ
記事の内容を表したイメージ(AI生成)

電話番号の一覧から市外局番だけを取り出したい、あるいは市外局番・市内局番・加入者番号の3つに分けて別々の列に整理したい、という場面があります。問題は、日本の固定電話は市外局番が2桁(03、06など)から5桁(例:0980)まで異なるため、単純に「左から○文字」と切り出すだけでは対応できない点です。

この記事では、ハイフンあり・ハイフンなし両方のケースを想定しながら、市外局番の桁数をどう判断するか、そして判断後にどう数式で切り出すかを具体的に示します。まず結論を言うと、「03」「06」など2桁の局番を持つ番号か、「045」など3桁か、といった判別は、番号のパターンを事前に整理してから数式に組み込むのが最も確実です。

作業前に必ずファイルをコピーして別名で保存してください。数式を貼り付けた後に元データを変えてしまうと確認が難しくなります。


日本の固定電話番号の構造を整理する

日本の固定電話番号(0AB〜J形式)は、市外局番・市内局番・加入者番号を合わせて10桁(0から始まる)になるように設計されています。ただし桁数の内訳は地域によって異なります。

表は横にスクロールできます

市外局番の桁数例残り(市内局番+加入者番号)の桁数
2桁03、068桁(4桁+4桁)
3桁045、0117桁(3桁+4桁)
4桁0424、04676桁(2桁+4桁)
5桁01234、098025桁(1桁+4桁)

加入者番号は原則4桁で固定されています。この「加入者番号は4桁」という規則を逆手に取ると、「ハイフンがある場合は最後のハイフン以降が4桁」という形で末尾から逆算できます。ただし一部例外(IP電話、特番など)もあるため、実際の名簿に当てはめるときは全件確認を行ってください。


ハイフンありの番号を分ける

方針

「03-1234-5678」のようにハイフンが2か所入っている場合、最初のハイフンより前が市外局番です。Excelの FIND 関数でハイフンの位置を見つけ、LEFT で切り出します。

=LEFT(A2, FIND("-", A2) - 1)

これだけで市外局番が取り出せます。

市内局番は1つ目のハイフンから2つ目のハイフンまでの間です。2つ目のハイフンの位置を取るには FIND を入れ子にします。

=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1) - FIND("-",A2) - 1)

加入者番号は末尾4文字なので RIGHT(A2,4) で取れます。

架空の例(架空の例です)

表は横にスクロールできます

セルA2の値市外局番市内局番加入者番号
03-1234-56780312345678
045-678-90120456789012
0467-23-45670467234567
09802-1-23450980212345

どの行も同じ数式で正しく切り出せています。ハイフン区切りが前提なので、番号の書式が統一されていることが条件です。混在している場合は後述のハイフンなし対応か、前処理で統一します。

迷いやすい例外

ハイフンが1つしかない(「0312345678」や「031234-5678」のような誤入力)とき、FIND は最初のハイフンしか見つけられず、市内局番の数式がずれます。作業前に LEN と SUBSTITUTE を組み合わせてハイフンが2つあるかチェックすると安全です。

=LEN(A2) - LEN(SUBSTITUTE(A2,"-",""))

この値が2になっていない行は要確認です。


ハイフンなしの番号を分ける

方針

「0312345678」のようにハイフンがない場合、市外局番の桁数が分からないと切り出せません。ここで使えるのが「先頭数字による分類」です。

日本の固定電話の市外局番は 0 から始まり、2桁目以降の数字によってある程度グループが分かれます。主要な判別ルールは次のとおりです。

  • 先頭2桁が 03 または 06 → 市外局番は2桁
  • 先頭2桁が 01 で3桁目が 1〜9(011、017 など)→ 3桁(ただし 011 は札幌)
  • 先頭が 0 で3桁目が数字パターンによる → 4桁または5桁

すべてのパターンを数式1本で自動判別するのは、メンテナンス性が下がるため推奨しません。現実的な方法は次の2つです。

方法1:局番桁数を別列に手入力(または参照)

局番桁数を管理する補助列(例:B列)を作り、2・3・4・5のいずれかを手で入力します。その値を使って市外局番を切り出します。

=LEFT(A2, B2)

この方法は数式がシンプルで、例外が出ても補助列を直すだけで済みます。件数が多い場合は、対象地域の局番桁数を一覧にしておくと入力作業が減ります。

方法2:主要2桁局番だけIF分岐で対処

件数が少なく、03・06・011・045など限られた局番しか登場しないと分かっている名簿なら、IFS(Excel 2019以降)または入れ子IFで分岐できます。

=IFS(
  OR(LEFT(A2,2)="03",LEFT(A2,2)="06"), LEFT(A2,2),
  OR(LEFT(A2,3)="011",LEFT(A2,3)="045",LEFT(A2,3)="052"), LEFT(A2,3),
  TRUE, "要確認"
)

画面の表示は版や設定により異なります。IFS が使えないバージョンでは IF を入れ子にしてください。この数式は「登録したパターン以外はすべて要確認」と返すため、抜け漏れを目視確認できます。

架空の例(架空の例です)

表は横にスクロールできます

A列(番号)B列(局番桁数)市外局番(=LEFT(A,B))
0312345678203
04567890123045
046723456740467
098021234540980

0980 は4桁局番の例です(沖縄の一部地域)。5桁局番は農村部などに存在しますが、実際に扱うときは総務省の番号計画資料で確認してください。


数式適用後の確認作業

数式を入れた後、次の点を確認します。

  1. 市外局番列の文字数が想定どおりか LEN で文字数を出し、全行同じ桁数になっているかフィルターで確認します。
  2. 元の番号を結合すると一致するか CONCATENATE(または&)で3列を結合し、元データと突合します。ハイフンあり版なら "-" を挟んで結合します。
  3. 要確認フラグが立っている行を処理する IFS 数式で「要確認」が返ってきた行は個別に目視で判断します。

個人情報が含まれる名簿を外部サービスや生成AIに貼り付けて判断させる場合は、社内ルールや利用規約を必ず確認してください。


よくある失敗と対処

先頭の 0 が消える セルの書式が「数値」になっていると、0312345678 が 312345678 と表示されます。該当列を選択して書式を「文字列」に変更し、再入力または TEXT 関数で補完します。すでに入力済みのデータがある場合、書式変更だけでは直らないことがあるため、列全体を選択 → 「文字列」に書式変更 → データをそのままコピー → 「値」として貼り直す手順を試してください。

ハイフンとダッシュが混在する 見た目が同じでも、「‐(全角ハイフン)」「-(全角マイナス)」「-(半角ハイフン)」が混在していると FIND("-",A2) が一部の行でエラーになります。SUBSTITUTE で統一してから数式を適用します。

=SUBSTITUTE(SUBSTITUTE(A2,"‐","-"),"-","-")

まずこの数式を補助列に出して、すべてのセルで半角ハイフンに統一されたことを確認してから、本番の切り出し数式に進みます。


次の行動

まず手元の名簿でハイフンがあるかないかを確認し、ある場合は LEN(A2)-LEN(SUBSTITUTE(A2,"-","")) でハイフンの個数を全行チェックしてください。件数が多くハイフンなし番号が混じる場合は、補助列に局番桁数を入力する方法が後から修正しやすいため、最初の選択として検討する価値があります。作業ファイルは必ず別名コピーを取ってから始めてください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ