市外局番に合わせて電話番号にハイフンを挿入する方法(TEXT関数&IF関数&LEFT関数)

今回は、さまざまな電話番号にハイフンをつける方法を解説していきます。

こんな人におすすめ!
  • 電話番号や郵便番号にハイフンをつけたい。
  • 固定電話の番号と携帯電話の番号が混在するけれど、ハイフンをつけたい。
  • 市外局番の桁数が違うけれども、ハイフンをつけたい。

今日の公式

1携帯電話の番号にハイフンをつける

携帯電話だけのリストなど、桁数が決まっている場合は、TEXT関数をつかってハイフンをつけることができます。

=TEXT(電話番号, 形式)
項目説明
電話番号電話番号の値、または電話番号の値が含まれるセルを指定します。
形式ハイフン付きの電話番号の形式を指定します。
携帯電話の場合は「000-0000-0000」といった形になります。

2固定電話や携帯電話など、複数の種類の電話番号に同時にハイフンをつける

固定電話や携帯電話など、市外局番の桁数もハイフンをいれる場所も違う電話番号が混在している場合は、さきほどのTEXT関数とLEFT関数、IF関数を組み合わせることで、出しわけることができます。

つまり

  1. 電話番号の上n桁が(LEFT関数)
  2. 「XXX」だったならば(IF関数)
  3. 電話番号を「000-000-0000」の形式に変換する(TEXT関数)

といった具合です。

下の公式では、2種類の市外局番と携帯電話の合計3種類の電話番号を出しわけていますが、IF関数を追加していくことでさらに多くの電話番号にハイフンをつけることができます。

=IF(LEFT(電話番号, 市外局番1の桁数)=“市外局番1”, TEXT(電話番号, 形式1), IF(LEFT(電話番号, 市外局番2の桁数)=“市外局番2”, TEXT(電話番号, 形式2), TEXT(電話番号, 形式3)))
項目説明
電話番号電話番号の値、または電話番号の値が含まれるセルを指定します。
市外局番1の桁数0を含む、市外局番1の桁数を指定します。
たとえば、神奈川県横浜市の場合は3桁です。
市外局番1市外局番の文字列を指定します。
たとえば、 神奈川県横浜市の場合は「045」です。
形式1ハイフン付きの電話番号の形式を指定します。
市外局番2の桁数0を含む、市外局番2の桁数を指定します。
たとえば、東京の場合は2桁です。
市外局番2市外局番の文字列を指定します。
たとえば、 東京の場合は「03」です。
形式2ハイフン付きの電話番号の形式を指定します。

電話番号にハイフンを挿入する方法の解説

今回は

  • 「03」で始まる東京の電話番号(03-XXXX-XXXX)
  • 「045」で始まる神奈川県横浜市の電話番号(045-XXX-XXXX)
  • 「090」「080」などで始まる携帯電話の電話番号(XXX-XXXX-XXXX)

が混在するリストにおいて、同時にハイフンを挿入するケースを考えます。

1まずは関数全体の設計をイメージする

いきなり関数を書き始める前に、IF関数の全体像をとらえておくことが大事です。

今回は3種類の電話番号を区別するので、以下のように考えます。

  • 上2桁が「03」のときは、「00-0000-0000」の形式にテキスト変換する
  • 上2桁が「03」ではなく、上3桁が「045」のときは、「000-000-0000」の形式にテキスト変換する。
  • 上2桁が「03」でもなく、上3桁が「045」でもないときは、「000-0000-0000」の形式にテキスト変換する。

21番目のIF関数を記述する

    全体設計に沿って、関数を組んでいきましょう。

    まずは

    • 上2桁が「03」のときは、「00-0000-0000」の形式にテキスト変換する

    桁数は「2」、形式は「00-0000-0000」を指定します。

    =IF(LEFT(D5, 2)=“03”, TEXT(D5, “00-0000-0000”), IF(LEFT(電話番号, 市外局番2の桁数)=“市外局番2”, TEXT(電話番号, 形式2), TEXT(電話番号, 形式3)))

    32番目のIF関数を記述する

    続いて、さきほどと同様に

    • 上2桁が「03」ではなく、上3桁が「045」のときは、「000-000-0000」の形式にテキスト変換する。

    のIF関数を書いていきます。

    =IF(LEFT(D5, 2)=“03”, TEXT(D5, “00-0000-0000”), IF(LEFT(D5, 3)=“045”, TEXT(D5, “000-000-0000”), TEXT(電話番号, 形式3)))

    4最後は携帯電話番号への変換を記述する

    • 上2桁が「03」でもなく、上3桁が「045」でもないときは、「000-0000-0000」の形式にテキスト変換する。
    =IF(LEFT(D5, 2)=“03”, TEXT(D5, “00-0000-0000”), IF(LEFT(D5, 3)=“045”, TEXT(D5, “000-000-0000”), TEXT(D5, “000-0000-0000”)))

    残りのセルにも関数をコピペして、正常にハイフンが挿入されることを確認しましょう。

    おすすめの記事