テック・ガジェット・写真とか

エクセルでハイフンを抜く方法。電話番号の0が消えるのは?

エクセルは電話番号をまとめるデータ名簿としても活用できるが、この電話番号のデータ記入時に数字と数字の間に使用することもある-(ハイフン)が少しクセモノだったりする。

というのも例えば、090-1234-5678と電話番号があった場合、このフォーマットではなく09012345678のようにハイフンなしのフォーマットに変えたい場合もあるかと思うが、置換でハイフンを抜いても09012345678と表示してくれない。

セルには、先頭の0が抜けた状態の9012345678が表示されてしまう。その対処法をいくつか紹介。

関連 エクセルで電話番号にハイフン入れる方法。郵便番号も

エクセルでハイフンを抜く方法

SUBSTITUTEを使う方法

「代える、取り替える」という意味を持つSUBSTITUTE関数を使用することで、ハイフンを抜いて先頭の0を残すことができる。

SUBSTITUTE関数の書式は以下の通り。

Copy
=SUBSTITUTE(セルを指定, "検索文字列", "置換文字列", "置換対象(省略化)")

下記のようにサンプル書式「=SUBSTITUTE(A1, “-“, “”)」を入力する。

A1サンプル書式表示結果
090-1234-5678=SUBSTITUTE(A1, “-“, “”)09012345678
SUBSTITUTEサンプルCopy
=SUBSTITUTE(A1, "-", "")

A1のセルを指定し、検索文字列は「-」と指定し、置換文字列は「ハイフン」を削除したいので何も入れずに「””(ダブルクオーテーション)」を入力する。

置換対象は、置き換える文字列の位置を指定することができるが、今回は使用しないので省略している。

置換を使う方法

置換を使う方法についても紹介してみようと思うが、「セルの書式設定」で「ユーザー定義」を使って行うものだが、これは携帯電話番号や固定電話が混ざっている場合には、あらかじめ携帯電話番頭と固定電話で別の列に分けておく必要がある。

ここではサンプルで携帯電話番号のハイフンを削除する方法について解説。

まずは、下記の手順で書式設定を開く。

  1. ①電話番号のセルをすべて選択する
  2. ②右クリックでメニューを開く
  3. ③セルの書式設定を選択

エクセルでセルの書式設定を選択

メニューが開いたら下記の手順でユーザー定義を行う。

  1. ①ユーザー定義を選択
  2. ②種類に「0000000000」(0を11桁)を入力。固定電話の場合には「0000000000」(0を10桁)を入力
  3. ③OKをクリック

エクセルでユーザー定義を指定

そしたら、キーボードの「Ctrl + H」で置換ウィンドウを開き、「検索する文字列」に「-」を入れ、「置換後の文字列」は空白のままにして「すべて置換」する。

エクセルでハイフンを置換

上記の手順で、携帯電話番号のハイフンを削除して表示することができる。

エクセルで電話番号の0が消える理由

電話番号のハイフンを削除する方法としては、上記の置換で「-」を削除する方法を紹介したが、これを実行すると例えば090-1234-5678を置換すると、09012345678となることを期待するのだが、そうとはならず先頭の数字の「0」が削除されて9012345678のように消えてしまう。

エクセルでは、初期設定では数字の先頭に「0」がある場合には自動で削除される仕様になっていて、これは、数字を計算するための数値としての取り扱いと、ひらがなやカタカナ、アルファベットのような文字列を取り扱っているためだと考えられる。

置換すると消えるのは、数値として扱うためで数値は頭に0が付かないからではないかと。

上述の「置換を使う方法」は、これを補うためにユーザー定義で先頭に「0」を」追加するようにしているのはこのためだ。

先頭の数字の0を残す方法

Excelのセルは通常であれば数字を数値として取り扱っているが、0を残す方法としていくつかのサンプルをピックアップしてみた。

数宇の中にハイフンなどの文字列を入れる

電話番号であれば、ハイフンのような記号などを入れれば、0は消えることなく残る。

ハイフンを入れるCopy
090-1234-5678

先頭に「’」を入れる

例えば、09012345678の先頭に「’」を入れる「’09012345678」と入力するとエラーが表示されるがそれを無視することで見た目には先頭の0を表示しつつ、「’」が表示されない裏技が使える。

手順は下記の通り。

  1. ‘09012345678と入力する
  2. セルを選択
  3. エラーをクリック
  4. エラーを無視するをクリック

エクセルで先頭にアポストロフィを入れる

セルの書式設定を文字列に設定

続いては、セルの書式を予め文字列として指定する方法。

文字列として取り扱いたいセルを選択した状態で右クリック、「セルの書式設定」を選択する。

  1. ①電話番号のセルをすべて選択する
  2. ②右クリックでメニューを開く
  3. ③セルの書式設定を選択

エクセルでセルの書式設定を選択

後は、分類の「文字列」を選択して「OK」。これで0を頭に入力しても、消えることはない。

エクセルで文字列を指定