営業リストの電話番号は、ハイフンの有無や全角数字、括弧などで書き方がばらばらになりがちです。ASCとSUBSTITUTEで数字だけの列を作る式、消えた先頭の0を戻すときにTEXTで10桁にそろえてはいけない理由、照合用と表示用の列の分け方を説明します。
「03-1234-5678」と「0312345678」は、Excelの重複の削除では別の番号として残ります。複数のところから集めた営業リストでは、同じ電話番号でも書き方がそろっていません。「03-1234-5678」「0312345678」「０３－１２３４－５６７８」「03(1234)5678」は、人が見れば同じ番号です。ところが Excel は、1文字でも違えば別の値として扱います。
書き方がばらばらのままだと、重複の削除や COUNTIF で同じ番号を見つけられません。以前に断られた相手の番号と突き合わせても、書き方が違えば一致しません。電話をかけたあと、どの行にかけたかを記録するときにも困ります。
答えは、元の列を残したまま、隣に「数字だけ」の照合用の列を作ることです。式は ASC と SUBSTITUTE を重ねるだけで作れますが、括弧が多く、消えた先頭の0の戻し方には落とし穴があります。以下では式を1つずつ組み立て、編集部の Excel で架空の番号を通した結果もあわせて載せます。
電話番号の書き方には、5つのゆれがある
営業リストの電話番号でよく見かけるゆれを、種類ごとに並べます。番号は説明のための架空のものです。
| ゆれの種類 | 書き方の例 |
|---|---|
| ハイフンのありなし | 03-1234-5678／0312345678 |
| 全角の数字や記号 | ０３－１２３４－５６７８ |
| 括弧 | 03(1234)5678／０３（１２３４）５６７８ |
| スペース | 03 1234 5678／03 1234 5678（全角スペース） |
| 先頭の0が消えたもの | 312345678 |
ハイフンの代わりに、長音の「ー」が入っていることもあります。見た目がハイフンに似ているため、手で入力するときに混ざります。
最後の「先頭の0が消えたもの」は、ほかのゆれとは出どころが違います。ハイフンのない電話番号を含む CSV をダブルクリックで開くと、Excel が番号を数値として扱い、先頭の0を落とします。これは後の章で扱います。
手順1：ASC で全角の文字を半角にする
電話番号がA列にあるとき、B列に次の式を入れて、最後の行までコピーします。
=ASC(A2)
ASC は、全角の文字を半角の文字に変える関数です。「０３－１２３４－５６７８」は「03-1234-5678」になります。全角の括弧や全角スペースも半角になり、長音の「ー」は半角の「ｰ」になります。
もともと半角の番号は、そのまま返ってきます。すべての行にこの式を入れて構いません。
手順2：SUBSTITUTE でハイフン・括弧・スペースを消す
SUBSTITUTE は、文字列の中の指定した文字を、別の文字に置き換える関数です。書き方は次のとおりです。
=SUBSTITUTE(文字列,検索文字列,置換文字列)
置換文字列に ""（何もない文字）を指定すると、その文字を消せます。たとえばB2のハイフンを消すなら、次の式になります。
=SUBSTITUTE(B2,"-","")
消したい文字は、ハイフンのほかに括弧の「(」「)」、長音が半角になった「ｰ」、スペースがあります。SUBSTITUTE は1回に1種類の文字しか置き換えられないので、式を重ねて使います。内側の SUBSTITUTE の結果を、外側の SUBSTITUTE の「文字列」に渡す形です。
2種類を消す式は、次のようになります。
=SUBSTITUTE(SUBSTITUTE(B2,"-",""),"(","")
内側の SUBSTITUTE(B2,"-","") でハイフンを消し、その結果から外側で「(」を消しています。同じ形で5種類まで重ね、手順1の ASC も中に入れると、次の式になります。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC(A2),"-",""),"(",""),")",""),"ｰ","")," ","")
この式をC列に入れれば、手順1のB列は要りません。一番内側の ASC(A2) から外へ向かって、ハイフン、「(」、「)」、「ｰ」、スペースの順に消しています。
式が長いので、括弧の数が合っているかを確かめる方法も書いておきます。"(" と ")" のように引用符で囲んだ括弧は、消す文字なので数えません。それ以外の括弧を数えると、「SUBSTITUTE(」が5つ、「ASC(」が1つで、開き括弧は6つです。閉じ括弧も、ASC(A2) の後ろに1つ、各 SUBSTITUTE の最後に1つずつで、合わせて6つになります。自分で消す文字を足すときは、一番外側に SUBSTITUTE( を1つ足し、最後に ,"消したい文字","") を足します。
先ほどの表の番号をこの式に通すと、先頭の0が消えたもの以外は、すべて「0312345678」になります。
手順3：消えた先頭の0は、TEXT で10桁にそろえずに戻す
CSV を開いたときに消えた先頭の0は、式で付け直せます。ただし、方法を選ばないと番号が壊れます。
よく紹介される方法に、TEXT 関数で桁数をそろえるものがあります。TEXT は、数値を指定した表示形式の文字列にする関数です。=TEXT(A2,"0000000000") と書くと、10桁に足りない分を先頭の0で埋めます。
固定電話だけなら、これで直ります。問題は携帯電話です。電話番号の桁数は、総務省の説明をまとめると次のとおりです。
| 番号の種類 | 始まり | 桁数 |
|---|---|---|
| 固定電話 | 0＋市外局番＋市内局番（合わせて5桁）＋加入者番号4桁 | 10桁 |
| 携帯電話 | 060、070、080、090 | 11桁 |
| フリーダイヤル | 0120 | 10桁 |
| フリーダイヤル | 0800 | 11桁 |
総務省のQ&Aでは、携帯電話とPHSは070・080・090で始まる11桁の番号で、2014年（平成26年）10月1日から番号による区別がなくなったと説明されています。060は、携帯電話の番号として後から追加された番号です。総務省の番号の種別の表では、4つとも「音声伝送携帯電話番号」にまとめられています。ふつうの電話番号は、10桁か11桁のどちらかです。
11桁の携帯電話の番号から0が消えると、10桁の数値になります。10桁の数値は、10桁にそろえる TEXT を通しても何も変わりません。
| 元の番号 | 0が消えた値 | =TEXT(A2,"0000000000") |
="0"&A2 |
|---|---|---|---|
| 0312345678（固定電話・10桁） | 312345678 | 0312345678 | 0312345678 |
| 09012345678（携帯電話・11桁） | 9012345678 | 9012345678（0が戻らない） | 09012345678 |
表の番号は架空のものです。11桁にそろえる形に変えても、今度は固定電話の番号の先頭に0が2つ付きます。桁数の違う番号が混ざっている以上、決まった桁数にそろえる方法は使えません。
国内の電話番号は、先頭に0が1つ付く形です。消えたのはその1つだけなので、「0を1つ前に付ける」直し方をします。
="0"&A2
& は、文字をつなげる記号です。A2の値の前に「0」をつなげます。
ただし、この式を全部の行に入れると、もともと0が付いていた番号にも0が足されます。先頭が0でない行にだけ付けるように、IF と LEFT を組み合わせます。手順2で数字だけにしたC列に対して、D列に次の式を入れます。
=IF(LEFT(C2,1)="0",C2,"0"&C2)
LEFT は、文字列の先頭から指定した数の文字を取り出す関数です。C2の1文字目が0ならそのまま返し、0でなければ前に0を付けます。これで、書き方のゆれと0の消えた番号が、まとめて数字だけの形にそろいます。
TEXT で作った値や、"0"& でつなげた値は、数値ではなく文字列になります。Microsoft の説明でも、TEXT は数値を文字列に変えるので、元の値は別のセルに残しておくのがよいとされています。
手順4：LEN で桁数を数え、そろわなかった行を見つける
最後に、D列の番号の桁数を確かめます。E列に次の式を入れます。
=IF(LEN(D2)=10,"",IF(LEN(D2)=11,"","要確認"))
LEN は文字数を返す関数です。10桁でも11桁でもない行にだけ「要確認」と入ります。フィルターで「要確認」の行を表示して、1行ずつ見ていきます。
桁数を数えるのは、0を付けたあとのD列にします。0を付ける前のC列で数えると、0の消えた携帯電話の番号（9012345678）が10桁なので、「要確認」になりません。
要確認になる行には、次のようなものがあります。
- 数字以外の文字が残っている：手順2の式で消していない記号がある。その記号を消す SUBSTITUTE を足す
- 内線番号やメモが付いている：「内線12」などが一緒に入っている。手で直す
- 2つの番号が1つのセルに入っている：どちらを使うかを決めて、手で直す
- 桁が足りない、または多すぎる：集めたときに番号の一部が欠けている。元の情報を確かめ直す
照合用の列と表示用の列を分けて持つ
ここまでで作った数字だけの列は、突き合わせのための列です。元の電話番号の列を上書きせずに、隣に「照合用」として置きます。
| 列 | 中身 | 使い道 |
|---|---|---|
| 元の電話番号 | 03-1234-5678 など、集めたときのまま | 電話をかけるとき、人が読むとき |
| 照合用 | 0312345678 のように数字だけ | 重複の判定、断られた相手のリストとの突き合わせ |
元の列を残す理由は2つあります。
1つは、ハイフンの入った番号のほうが、電話をかける人が読み間違えにくいからです。
もう1つは、数字だけの番号からハイフンの入った番号を作り直すのが難しいからです。総務省の説明では、市外局番は1〜4桁、市内局番も1〜4桁で、地域によって区切りの位置が違います。「0312345678」を「03-1234-5678」と区切るのか、別の位置で区切るのかは、数字だけでは決まりません。先頭から何桁目にハイフンを入れる、という決まった式では作れないのです。
照合用の列を作ったら、その列を使って次の作業をします。
- 照合用の列で
=COUNTIF(D:D,D2)のように件数を数え、同じ番号の行を見つける（手順3のD列が照合用の場合） - 以前に断られた相手の番号も同じ式で数字だけにし、照合用の列どうしで突き合わせる
- ほかのソフトに渡すときは、［ファイル］→［名前を付けて保存］で「CSV UTF-8 (コンマ区切り) (*.csv)」を選んで保存する
保存した CSV をダブルクリックで開き直すと、先頭の0がまた消えます。開き直すときは、［データ］タブの「テキストまたは CSV から」で取り込み、電話番号の列のデータ型を「テキスト」にしてください。
リストハブでは、見積りの画面で「電話番号が同じ」行をまとめてから購入できます（30万件を超える条件では使えません）。自社でほかのリストと合わせるときは、この記事の式で照合用の列を作ってから突き合わせてください。
迷ったら「元の列は触らない」
電話番号の整え方で取り返しがつかなくなるのは、元の列を上書きしたときだけです。照合用の列は何度作り直しても構いませんが、ハイフンの位置は数字だけの番号からは戻せません。式を試すときも、0を付けるときも、元の列はそのまま残しておきます。そのうえで、照合用の列で重複を数え、断られた相手のリストと突き合わせます。
照合用の列を使った重複のまとめ方は「テレアポ用のリストの記事」と「Excelで営業リストの重複を消す方法」に、CSV の開き方は「CSVをExcelで開くと文字化けするときの直し方」にあります。電話番号が載っている会社の数は、トップページの検索で地域と業種を選ぶと確かめられます。
確かめた資料（2026年9月30日に確認）
- Microsoft サポート「ASC 関数」
- Microsoft サポート「SUBSTITUTE 関数」
- Microsoft サポート「TEXT 関数」
- Microsoft サポート「LEN 関数、LENB 関数」
- Microsoft サポート「LEFT 関数、LEFTB 関数」
- Microsoft サポート「IF 関数」
- Microsoft サポート「COUNTIF 関数」
- Microsoft サポート「.csv ファイルを使用して Outlook で連絡先をインポートまたはエクスポートする」（「CSV UTF-8 (コンマ区切り) (*.csv)」で保存する手順）
- Microsoft サポート「先頭のゼロおよび大きい数値の保持」
- 総務省「電話番号に関するQ&A」（固定電話・携帯電話・0120・0800の桁数、市外局番と市内局番の桁数、携帯電話とPHSの番号の区別がなくなったこと）
- 総務省「電気通信番号制度」（番号の種別。060・070・080・090は「音声伝送携帯電話番号」）
- 総務省「携帯電話番号への060番号の追加」
記事の式は、編集部の Windows の Excel で、架空の番号を入れて結果を確かめています。ASC で全角の括弧・スペースや長音の「ー」がどう変わるかは、この確認で見た結果です。
この記事は、営業リストの作成サービス「リストハブ」を運営するミルブレインズ合同会社の編集部が書いています。数字は、リストハブに収録している企業・店舗のデータを編集部が数えたものです。数え方と編集方針は「データの集め方・数え方と編集方針」にまとめています。誤りに気づいた方は、運営会社のページからお知らせください。