CSVファイルをスプレッドシートやエクセルで読み込むと、セルの値の冒頭のゼロがなくなるということが発生します。
例えば「0630100」というデータが「630100」になったり、「000123」が「123」になったりします。
これを修正する方法についてまとめています。
発生する原因
CSVファイルをスプレッドシートやエクセルなどで開くとデータの冒頭の0がなくなる原因はアプリ側で文字列のデータを数値に自動変換しているためです。
例えば、郵便局のHPから郵便番号と都道府県、市町村、町域などが連動したデータをCSVファイルとしてダウンロードすることができます。
このファイルをメモ帳で開くと以下のようになります。
3列目の郵便番号を見ると「”0600000″」のようにダブルクオテーションで囲み、文字列になっていることがわかります。
このファイルをスプレッドシートやエクセルで開くと次のようになります。
冒頭の0が消えてしまっています。
この状態だと「064-0941」という郵便番号を検索すると、該当する郵便番号がないということになってしまいます。
このとき書式設定は「自動」になっており、数値に変換されています。
対処法｜カスタム数値形式
対処法はとても簡単です。冒頭の0が消えてたデータを含む列を選択した状態で、上部メニューの「123」→「カスタム数値形式」を選択します。エクセルの場合は「セルの書式設定 → ユーザー定義」です。
入力欄に本来の数値の桁の数だけ「0」を入力します。
例えば、郵便番号のように7桁のデータであれば、「0000000」（0を7つ）入力し「適用」をクリックします。
これで冒頭の0が表示されます。
TEXT関数
上記はCSVファイルのデータをカスタム数値形式で直接「0000000」のように、本来の数値の桁の数だけ0を指定しました。
こうした数値形式の設定はTEXT関数でも行うことができます。
TEXT関数の基本構文は次のようになっています。
=TEXT(対象のデータ, "数値形式")
例えば、A2セルのデータを郵便番号のように7桁のデータでの表示に変更したければ、第2引数で「0000000」（0を7つ）入力します。
=TEXT(A2, "0000000")カスタム数値形式やユーザー定義の0や#は何を意味しているか？
０の意味｜0を表示する
スプレッドシートやエクセルにおけるカスタム数値形式やユーザー定義の0が何を意味しているかというと、ゼロを対応する数字で置き換え、数字が存在しない場合はゼロを表示するという指定になります。
実例1
text("001030", "00000")↓ 結果
001030実例2
text("0012.1", "0000.00")↓ 結果
0012.10#の意味｜意味を持たないゼロは省略する
一方、#を使った場合は、#を対応する数字で置き換え、数字が存在しない場合は何も表示しないという指示になります。
実例
text("001030", "######")↓ 結果
1030
「001030」の冒頭の「00」は意味を持たないので省略されます。
実例2
text("0012.1", "####.##")↓ 結果
12.1#と0を一緒に指定する
#と0は個別ではなく一緒に使うことができます。
実例１
例えば、数値が0のときは0を表示するようにする場合は、末尾のみ「0」にします。
text("0000", #####0")↓ 結果
0実例2
例えば、3桁は絶対表示したいけど、それより多い桁は実際の数値に合わせる場合は以下のように記述します。
text("000030", "###000")↓ 結果
030実例3
例えば、小数点は2桁で固定したい場合は以下のようにします。
text("23.1", "###.00")↓ 結果
23.103桁ごとに区切る
なお、カスタム数値形式の「0」も「#」のどちらも、「,」を使うことで数値を3桁ごとに区切る指定をすることができます。
実際に指定するときは3桁という規則はなく、「,」を挿入した位置にカンマが表示されます。
実例1
text("0012034", "0,000,000")↓ 結果
0,012,034実例2
text("0012034", "#,###,0")↓ 結果
12,034