テクノロジー・動画・写真・デザインとか

エクセルで-(ハイフン)を抜いて電話番号の先頭の0を残す方法

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

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

セルには、先頭の0が抜けた状態の9012345678が表示されるだけが、いくつかの方法がある。

SUBSTITUTEの関数を使ってハイフンを抜いて先頭の0を残す方法

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

SUBSTITUTEの書式

SUBSTITUTE(文字列, "検索文字列", "置換文字列", "置換対象(省略化)")

続きを読む

サンプル解説

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

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

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

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

ユーザー定義を使う方法

上記のSUBSTITUTE関数よりも手軽かもしれないのが、この「セル書式設定」の「ユーザ定義」を使った方法。

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

続いては、先頭に0を入れたいセルや列を選択した状態で右クリック、「セルの書式設定」を選択する。
ちなみによく使う書式設定のショートカットキーは[Ctrl]+[1]なので、覚えておくと便利。

セルの書式設定を選択

そしたら、「ユーザー定義」を選択して「種類」の欄に「00000000000」と、「0」を11桁入れるだけ。
ユーザー定義

以上がエクセルでハイフンを抜いて電話番号の先頭の0を残す方法の紹介だが、以下は、なぜ0が消えるのかという解説。

Excelで電話番号の先頭の0が表示されない理由

結論から言うと先頭の0は意味がないから、Excelはそれを無視している。笑。

単純に「090 + 100」と「90 + 100」という計算をすると結果は同じになるので、不要。

それでは、なぜ先頭に0を残せるのか?

Excelは数字を数値と文字列の2種類を取り扱う

Excelは、数字を計算するための数値としての取り扱いと、カナ文字やアルファベットのような文字列として2種類の数字を取り扱っている。

なので先頭に0が表示できるのは、この「数字を文字列として取扱っている」ため。

数字を文字列として取り扱うその他の方法

Excelのセルは通常であれば数字を数値として取り扱っているが、数字をSUBSTITUTE関数を使わずに文字列として取り扱う方法が他にもある。

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

電話番号であれば、ハイフンのような記号などを入れれば文字列として認識してくれる。

2.) 先頭に「'(シングルクォーテーション)」を入れる

例えば、「’09012345678」のように電話番号の先頭に「'(シングルクォーテーション)」をいれることでも、数字を文字列として取り扱うことができる。

シングルクォーテーションを入れると、このようにセルの左上の緑三角のエラーが表示されてしまう。
三角の緑はエラー表示!

これを非表示にするには、B1のセルを選択するとエラーマークが表示されるので、それをクリックして「エラーチェックオプション」を選択する。

エラーチェックオプション

続いては、「数式」の「エラーチェック」のチェックを外す。
エラーチェックを外す!

チェックを戻したい場合は、メニューの「ファイル」、「オプション」。

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

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

後は、分類の「文字列」を選択して「OK」。
文字列を選択

カテゴリ:エクセル関数