CSVをExcelで読み込んだ際に先頭の0が消える（0落ちする）場合の対処方法
CSVをExcelで開いたときに電話番号や郵便番号の先頭の0が表示されず困ったことはありませんか？
今回は対処方法を3つお伝えします。
INDEX
セル内に入力されている先頭の0が消えてしまう（0落ちする）原因
CSVファイルをExcelで開いたときにExcelが勝手にデータの型を変更して表示してしまいます。
実際の例を見てみましょう。
以下がCSVファイルをテキストエディタで表示した状態です。
このデータであれば本来以下のように表示されてほしいとこです。
しかし、実際にExcelで開いてみると・・・
このように「No.」、「郵便番号」、「TEL」の箇所で先頭の0が無くなって表示されています。
これを「0（ゼロ）落ち」とも呼びます。
この現象はExcelがCSVのデータを開く際に、セルの表示形式を「標準」にしてしまうことで起こります。
「標準」にすると、入力した値を基にExcelが判断した書式を割り当てるということのようです。
結果的に「数値」に置き換えられてしまい先頭の「0」が省かれてしまいます。
先頭の「0」が省かれてしまったセルを「セルの書式設定」で「文字列」に変更しても省かれてしまった「0」は表示されません。
【対処方法1】データ取り込み時に列の書式を指定する
まずは比較的よく目にする対処方法をお伝えします。
1．空のExcelを開きます
まず初めに新しくExcelを立ち上げます。
2．外部データの取り込みでcsvファイルを指定します
上部の「データ」タブを選択し、「外部データの取り込み」にある「テキストファイル」をクリックして該当のcsvファイルを「インポート」します。
「データ」タブの「テキストファイル」をクリック。
該当のCSVを「インポート」します。
3．テキストウィザードで列の書式を指定します
インポートをクリックすると「テキストファイルウィザード」が立ち上がります。
「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択した状態で「次へ」をクリックします。
「区切り文字」のエリアで「カンマ」にチェックをいれて、「次へ」をクリックします。
「データのプレビュー」エリアで文字列にしたい列を選択して「列のデータ形式」エリアで「文字列」を選択します。
対象の列全てで「文字列」を指定したら「完了」をクリックします。
「データの取り込み」が表示されるので「既存のワークシート」で =$A$1 を選択して「OK」をクリックします。
期待する形でデータが表示されました。
ただし、セル内に改行がある場合は要注意！
上記の例のように1セルのデータ内に改行が無いデータであればうまくいきますが、セル内に改行が存在する場合に上記の手順を行うと以下のようになってしまいます。
CSVデータ（備考欄に改行を含む文字列がある）
期待する結果
実際の結果
CSVを普通にExcelで開いた際には、1セル内に改行を含む文字列が正常に表示されますが、「データの取り込み」で「テキストファイル」を選択して取り込んだことにより、改行がそのまま解釈されて次の行に表示されてしまいます。
これでは先頭の0が省かれてしまう問題が解決しても、このままでは使えるデータではありません。
上記に対応するには次の対処方法が必要となります。
【対処方法２】元データを「=””」で囲んで最初から文字列として認識させる方法
改行を含んだデータがある場合には「データ取り込み」の時点ですでに文章が次の行になってしまい対処のしようがないので、元々のCSVファイルのデータを変更することで対処します。
具体的にはCSVファイル内の1セルに対応するデータを =”データ” の形式にすることで、CSVファイルをExcelが読み込んだときに自動で「文字列」として認識してくれます。
「=」で始まる項目はExcelの数式としてとらえられ、セルに対して =”データ” といった数式を入れることになり、結果的に ”データ” という部分が文字列として扱われます。これによって先頭の「0」が省かれることもなくなります。
今回は数字だけのセルの値を =”” で囲む方法を説明します。
手順は以下になります。
※あくまで例であり、文字列にしたいセルの値が =”” で囲まれればよいので他のテキストエディタでも問題ありません。
1．正規表現の使えるテキストエディタを用意します
今回は無料で高機能なエディタ「サクラエディタ」を利用します。
※V2(Unicode版)の最新版をダウンロードしてインストールしてください。
2．対象のcsvをエディタで開きます
今回は3桁以上の数字を「=””」で囲んでいきます。
3．正規表現を使った置換をおこないます
「検索」⇒「置換」をクリックします。
- 「正規表現」にチェックをつけます。
- 置換前のテキストボックスに\d{3,}、置換後のテキストボックスに=”を入力します。
- 置換対象で「選択始点(1)挿入」を選択します。
※ここでの数字の3は3桁を表しています
この状態で「すべて置換」をクリックします。
対象の数字の前方に「=”」が挿入されました。
続いて、対象の数字の後方に「”」を挿入します。
- 「検索」⇒「置換」を再度選び、「正規表現」にチェックをつけます。
- 置換前のテキストボックスに\d{3,}、置換後のテキストボックスに”を入力します。
- 置換対象で「選択終点(2)追加」を選択します。
この状態で「すべて置換」をクリックします。
数字の末尾に「”」が追加されました。これで置換が完了です。
データは以下のようになります。
この状態でCSVファイルを保存します。
4．Excelでファイルを開く
先ほど保存したCSVをExcelで開きます。
「0」が省かれること無く、期待する結果で表示されました。
【対処方法3】Google スプレッドシートに一旦取り込む
3つ目の方法としてはGoogleのスプレッドシートにCSVをインポートするという方法があります。
この方法であれば、セル内改行がある場合でも、0落ちすることなく簡単に対処することができます。
GoogleスプレッドシートはGoogleアカウントが必要です。
https://www.google.com/intl/ja_jp/sheets/about/
1．Googleスプレッドシートを新しく開きます
2．CSVファイルをアップロードします
次に「ファイル」⇒「インポート」を選択します。
「ファイルをインポート」で「アップロード」タブを選択し、対象のCSVファイルをドラッグしてアップロードを行います。
3．インポートを行います
- 「場所のインポート」の「スプレッドシートを置換する」をチェックします。
- 「区切り文字の種類」の「カンマ」をチェックします。
- 「テキストを数値、日付、数式に変換」の「いいえ」をチェックします。
上記の状態で「データをインポート」をクリックします。
これですべてのセルがテキストとしてインポートされました。
後は「ファイル」の「形式を指定してダウンロード」で「Microsoft Excel」を選択すればExcelファイルがダウンロードできます。
ファイルのダウンロード後はGoogleスプレッドシートからシートを削除しておきます。
さいごに
いかがだったでしょうか？今回は3つの対処方法をお伝えしました。
2つ目の対処方法は数字3桁以上のデータを対象としましたが、「正規表現」を覚えることにより柔軟な指定方法も可能になりますのでお試しください。
参考：正規表現
http://www-creators.com/archives/4278
3つ目の方法はシンプルでわかりやすいのですが、外部に一時的にデータをアップすることになりますので事前に社内の規定などを確認してから実施してください。
CSVでデータと取り扱うとたまに遭遇する現象なのでお役に立てれば幸いです。
この記事を書いた人
- 創造性を最大限に発揮するとともに、インターネットに代表されるITを活用し、みんなの生活が便利で、豊かで、楽しいものになるようなサービスやコンテンツを考え、創り出し提供しています。
この執筆者の最新記事
関連記事
最新記事
FOLLOW US
最新の情報をお届けします
- facebookでフォロー
- Twitterでフォロー
- Feedlyでフォロー