以前にエクセルでハイフンを抜く方法。電話番号の0が消えるのは?を紹介したのが、今回は逆にエクセルで電話番号や郵便番号に-(ハイフン)を入れる方法を紹介したいと思う。
電話番号や郵便番号にハイフンを入れる方法をまとめみた。
電話番号にハイフンを自動挿入するツール
エクセルで電話番号にハイフンを入れる方法だが、後述でいくつやり方を紹介しているが、携帯の電話番号や日本全国の固定電話の市外局番に対応しようとするとエクセルだけでは難しい。
そこで、携帯の電話番号、日本全国の固定電話番号別に自動でハイフンが挿入されるツールを作ってみた。
使い方だが、エクセルの電話番号の一覧の列をコピーして貼り付けて、コピーボタンを押すだけで簡単にハイフンを挿入したデータをコピーできるようになっている。
セルの書式設定のユーザー定義を使う方法
まずは、「セルの書式設定」の中にある「ユーザー定義」を使うことで、簡単に電話番号にハイフンを入れることができる方法。
作業に入る前に、携帯電話番号と固定電話がある場合には別々のセルに準備しておこう。
サンプルで携帯の電話番号にハイフンを挿入する手順。そしたら下記手順でセルの書式設定を開く。
- 携帯電話の電話番号が入力されている①セルの列を選択して右クリック
- メニューが表示されたら、②セルの書式設定をクリック
続いては、ユーザー定義と選択して「種類」の入力欄に000-0000-0000のように入力してOKクリック。
携帯電話番号の場合
000-0000-0000
OKをクリックすると「090-1234-5678」のように表示されているかと。
固定電話の場合
固定電話の場合だが、地域によって市外局番の桁数が異なるため、それに合わせてハイフンの位置を変更する必要がある。ちなみに、固定電話の数字の桁数は10だ。
固定電話番号の例
00-0000-0000
固定電話番号の例
000-000-0000
郵便番号の場合
郵便番号の例
000-0000
この方法は手軽であるが、全国の固定電話の市外局番に対応しようとすると難しいものがあるので、後述のツールを使ってハイフンを挿入するやり方をおすすめ。
LEFT、MID、RIGHT関数を使う方法
これも比較的に手軽な方法で、LEFT、MID、RIGHT関数の関数を使うことでハイフンを入れることができる。これらの関数は以下の役割があり、組み合わせて使う事で電話番号にハイフンを入れられる。
- LEFT関数
- 指定した文字列を左から指定した文字数分を抜き出す
- MID関数
- 指定した文字列の指定した位置から指定した文字数分を抜き出す
- RIGHT関数
- 指定した文字列を右から指定した文字数分を抜き出す
サンプルで携帯の電話番号にハイフンを挿入。そしたら下記手順でセルの書式設定を開く。
- 携帯電話の電話番号が入力されている①セルの列を選択して右クリック
- メニューが表示されたら、②セルの書式設定をクリック
そしたら文字列を選択して、OKをクリック。
そしたら、携帯電話番号を入力するか貼り付ける。なお、エクセルなどからコピペした場合は書式は「貼り付け先の書式に合わせる」を選択しておこう。
そしたら、下記の書式をB1に貼り付ける。B1に貼り付けた書式をB2〜B3のセルにコピペする。
携帯電話番号の場合
=LEFT(A1,3)&"-"&MID(A1,4,4)&"-"&RIGHT(A1,4)
するとハイフン入りの携帯電話番号が表示されるかと。
関数で使う方法も、全国の固定電話の市外局番や携帯電話番号に対応しようとすると難しいものがあるので後述のツールを使うことをおすすめ。
郵便番号の場合
郵便番号の場合
=LEFT(A1,3)&"-"&RIGHT(A1,4)
固定電話と携帯携帯にハイフンを入れる方法
限定的だが、例えば東京と大阪の市外局番03と06に該当する電話番号と携帯電話番号が混ざっていると分かっている場合に、東京と大阪の電話番号は03-1234-5678のように表記し、それ以外の携帯は090-1234-5678のように表記させることができる。
=TEXT(A1*1,IF(OR(LEFT(A1,2)="03",LEFT(A1,2)="06"),"00-0000-0000","000-0000-0000"))
軽く書式の解説。
TEXT関数についても理解しておくと、このサンプルの書式をより理解できる。
さて、この書式で使用しているTEXT関数は、数値を文字列に変換してくれるもので、TEXT関数の書式は「TEXT(数値, 表示形式)」となっている。
しかしA1のセルは文字列となっているので、これを「数値」にしなければならない。
そこで「A1*1」とあるように、A1の文字列に「1」を掛け算してあげる事で数値にすることができる。
続いては、TEXT関数の表示形式部分に下記を実現するために、IF関数のORとLEFT関数を使用している。
「番号の中に03か06があれば00-0000-0000と表記し、なければ000-0000-0000と表記」
IFのORの書式は以下の通りとなり、
=IF(OR(条件1,条件2),値1,値2)
「条件1、または条件2の場合は値1を返し、そうでない場合は値2を返す」という機能になる。
ここでの条件1は、「LEFT(A1,2)=”03″」で「A1の左から2番目までの数字が03なら」となり、条件2は「LEFT(A1,2)=”06″」で「A1の左から2番目までの数字が06なら」となる。
そして、これらのいずれかの条件に該当するなら値1の「00-0000-0000」を表示し、そうでなければ「000-0000-0000」を表示する。
以上がこの書式の説明になるが、残念ながら、これらでは日本全国の電話番号にエクセルでハイフンを入れるのは難しいので次のツールを使う方法をおすすめ。