電話番号の一覧から市外局番だけを取り出したい、あるいは市外局番・市内局番・加入者番号の3つに分けて別々の列に整理したい、という場面があります。問題は、日本の固定電話は市外局番が2桁(03、06など)から5桁(例:0980)まで異なるため、単純に「左から○文字」と切り出すだけでは対応できない点です。
この記事では、ハイフンあり・ハイフンなし両方のケースを想定しながら、市外局番の桁数をどう判断するか、そして判断後にどう数式で切り出すかを具体的に示します。まず結論を言うと、「03」「06」など2桁の局番を持つ番号か、「045」など3桁か、といった判別は、番号のパターンを事前に整理してから数式に組み込むのが最も確実です。
作業前に必ずファイルをコピーして別名で保存してください。数式を貼り付けた後に元データを変えてしまうと確認が難しくなります。
日本の固定電話番号の構造を整理する
日本の固定電話番号(0AB〜J形式)は、市外局番・市内局番・加入者番号を合わせて10桁(0から始まる)になるように設計されています。ただし桁数の内訳は地域によって異なります。
表は横にスクロールできます
| 市外局番の桁数 | 例 | 残り(市内局番+加入者番号)の桁数 |
|---|---|---|
| 2桁 | 03、06 | 8桁(4桁+4桁) |
| 3桁 | 045、011 | 7桁(3桁+4桁) |
| 4桁 | 0424、0467 | 6桁(2桁+4桁) |
| 5桁 | 01234、09802 | 5桁(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-5678 | 03 | 1234 | 5678 |
| 045-678-9012 | 045 | 678 | 9012 |
| 0467-23-4567 | 0467 | 23 | 4567 |
| 09802-1-2345 | 09802 | 1 | 2345 |
どの行も同じ数式で正しく切り出せています。ハイフン区切りが前提なので、番号の書式が統一されていることが条件です。混在している場合は後述のハイフンなし対応か、前処理で統一します。
迷いやすい例外
ハイフンが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)) |
|---|---|---|
| 0312345678 | 2 | 03 |
| 0456789012 | 3 | 045 |
| 0467234567 | 4 | 0467 |
| 0980212345 | 4 | 0980 |
0980 は4桁局番の例です(沖縄の一部地域)。5桁局番は農村部などに存在しますが、実際に扱うときは総務省の番号計画資料で確認してください。
数式適用後の確認作業
数式を入れた後、次の点を確認します。
- 市外局番列の文字数が想定どおりか
LENで文字数を出し、全行同じ桁数になっているかフィルターで確認します。 - 元の番号を結合すると一致するか
CONCATENATE(または&)で3列を結合し、元データと突合します。ハイフンあり版なら "-" を挟んで結合します。 - 要確認フラグが立っている行を処理する
IFS数式で「要確認」が返ってきた行は個別に目視で判断します。
個人情報が含まれる名簿を外部サービスや生成AIに貼り付けて判断させる場合は、社内ルールや利用規約を必ず確認してください。
よくある失敗と対処
先頭の 0 が消える セルの書式が「数値」になっていると、0312345678 が 312345678 と表示されます。該当列を選択して書式を「文字列」に変更し、再入力または TEXT 関数で補完します。すでに入力済みのデータがある場合、書式変更だけでは直らないことがあるため、列全体を選択 → 「文字列」に書式変更 → データをそのままコピー → 「値」として貼り直す手順を試してください。
ハイフンとダッシュが混在する 見た目が同じでも、「‐(全角ハイフン)」「-(全角マイナス)」「-(半角ハイフン)」が混在していると FIND("-",A2) が一部の行でエラーになります。SUBSTITUTE で統一してから数式を適用します。
=SUBSTITUTE(SUBSTITUTE(A2,"‐","-"),"-","-")
まずこの数式を補助列に出して、すべてのセルで半角ハイフンに統一されたことを確認してから、本番の切り出し数式に進みます。
次の行動
まず手元の名簿でハイフンがあるかないかを確認し、ある場合は LEN(A2)-LEN(SUBSTITUTE(A2,"-","")) でハイフンの個数を全行チェックしてください。件数が多くハイフンなし番号が混じる場合は、補助列に局番桁数を入力する方法が後から修正しやすいため、最初の選択として検討する価値があります。作業ファイルは必ず別名コピーを取ってから始めてください。
参考資料
- LEFT 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- MID 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- RIGHT 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- LEN 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
