Excel使いこなしの基礎と応用

Excelで数値の冒頭の0が省略されてしまった時の対処法

使用関数:REPLACE、LEN、IF

住所録の郵便番号や電話番号を数値で入力すると、先頭の「0」が省略されてしまいます。「0」から始まるデータだと資料として印刷する時などに困るので、これを関数で補います。

目次


REPLACE関数で郵便番号を修正

北海道札幌内の郵便番号は「0」で始まるものが多いので、下図のように6桁表示になってしまっています。郵便番号の列を増設して、先頭の0を補った表示に修正しましょう。
先頭が0の郵便番号が6桁表示になる

増設した「郵便番号」の最初のセルを選択して、「数式」タブ「関数ライブラリ」「文字列操作▼」から「REPLACE」をクリックします。
「関数ライブラリ」「文字列操作▼」の「REPLACE」をクリック
REPLACE関数の引数ダイアログが開きます。
最初の引数「文字列」には修正したい元データの6桁郵便番号が入ったセル「C2」を指定します。
REPLACEの引数「文字列」に6桁郵便番号が入ったセルを指定

次の引数「開始位置」は文字を置き換える位置情報を文字数単位(またはバイト数単位)で指定します。開始位置は文字列の始まる位置を「1」とし、1文字ごとに順次2,3…と割り振られます。
REPLACEの開始位置
この場合は先頭の「1」を指定します。
REPLACEの引数「開始位置」に1と指定

次の引数「文字数」は開始位置から何文字を置き換えるかの指定です。
この場合はそのまま先頭に0を置きたいので、文字数は「0」です。
REPLACEの引数「文字数」に0を指定

最後の引数「置換文字列」は挿入したい文字を指定します。
ここでは、もちろん「0」ですね。
REPLACEの引数「置換文字列」に0を指定

数式は「=REPLACE(C2,1,0,0)」です。

数式を下までフィルコピーして完了・・・
数式をフィルコピー

・・・ですが、実務では7桁の郵便番号が混在しているのが普通です。REPLACEの数式だけでは7桁の番号にも「0」が付いてしまうので、IF関数にネストしましょう。

6桁と7桁の郵便番号が混在する表ではIFに入れ子

郵便番号が6桁の場合は冒頭に「0」を挿入し、7桁の場合はセル内容そのままを返す」というIF文を組み立てます。

IF関数のダイアログで構成を考えてみましょう。
上記IF文を分割してそれぞれの引数に当てはめてみると、
郵便番号が6桁の場合は」が「論理式」、「真の場合」は「冒頭に「0」を挿入」、「偽の場合」は「セル内容そのままを返す」ですね。
IF関数のダイアログで引数にIF文をあてはめる

「真の場合」の「冒頭に「0」を挿入」は前章で組み立てたばかりのREPLACEの数式。
「偽の場合」の「セル内容そのままを返す」は郵便番号の入ったセル「C2」。
IF関数の「真の場合」「偽の場合」を入力

問題は「論理式」の「郵便番号が6桁の場合は…」ですが、これには文字列の文字数を返すLEN関数を入れます。LEN関数は引数が一つだけのシンプルな構成。
LEN関数のダイアログ
この数式「LEN(C2)」を「論理式」に入れて、続けて「=6」と入力します。
LEN(C2)=6」→「郵便番号文字数が6であれば」
IF関数の論理式を構築

最終的な数式は「=IF(LEN(C2)=6,REPLACE(C2,1,0,0),C2)」となります。

数式をフィルコピーすると、冒頭に0を付けた6桁郵便番号が左寄りになり、数値から文字列に変わったことが分かります。7桁の方は右寄りのままですね。
数式をフィルコピーした郵便番号の列
体裁を整えるため、D列を中央揃えにしてC列を非表示にしておきます。
 体裁を整えるため、中央揃え

▶SUBSTITUTE関数で複数の文字列を一括で置き換える
▶社名の一覧から「株式会社」を削除する


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。