こんにちは、IT担当のてけてけです。
今日は、Excel関数を利用して郵便番号をハイフンなし（1234567）からハイフンあり（123-4567）に一括で加工する方法を紹介します。
データのインポートは地味に大変
今やとても多くの管理システムが存在していますね。給与管理システムや勤怠管理システム、財務会計システムなど、利用されている会社は多いかと思います。
弊所でもいくつかのシステムを利用していますが、いつも地味に大変なのがデータのインポートです。
大体のシステムでは保持しているデータをCSVファイルでエクスポートしたり、インポートしたりすることができます。
あるシステムからエクスポートしたCSVファイルを、そのまま別システムにインポートすることができればいいんですが、多くの場合は、エクスポート側とインポート側でCSVファイルのフォーマットや項目の出力形式が異なり、取り込む前に加工する必要が出てきます。
項目の順序を変えるだけであれば簡単ですが、項目の出力形式まで違うと大変です。例えば、氏名項目は苗字と名前の間に全角スペースを入れているが、インポート側では苗字と名前を別々の項目に分けないといけない・・・など。こんなちょっとした違いでも、データ量が多ければ多いほど、手で１つずつ修正するには時間がかかります。
インポートするCSVファイルの郵便番号の形式がバラバラだった・・・
弊所で利用している人事奉行では郵便番号の形式は「3桁-（半角ハイフン）4桁」になっています。そのため、インポートするCSVファイルの郵便番号も、「000-0000」の形式になってないといけません。
ただ、先日インポートしようとしたCSVファイルは、次のようになっていました。
※画像のデータはサンプルです。
どうやらエクスポート側のシステムは郵便番号は自由入力だったようで、ハイフンあり・なしのデータが入り混じっています。必須入力にもなっていなかったようで、空欄もあります。
酷いものだと、桁数が足りていないデータがあります。ただ、これはエクスポート側のシステムの問題ではなく、CSVファイルをExcelで開いてそのまま保存するとよく起きる現象です。
頭が0で始まる数字データは、Excel側が勝手に頭の0を切り取ってしまいます。北海道や東北の一部などは、郵便番号が０で始まるので、うっかり保存してしまうとこうなります。
郵便番号にハイフンを入れる数式を作ってみた
というわけで、次のルールに従って、Excelでハイフンありの郵便番号に変更できるようにしてみました。
- ハイフンありの郵便番号は、そのまま郵便番号を出力
- ハイフンが無い郵便番号は、ハイフンを入れて出力
- 空欄は、そのまま空欄で出力
- 桁数が足りない郵便番号は、頭をゼロ埋めしてからハイフンを入れて出力
作った数式は、次の通りです。
=IF(A1 = "", "", IF(ISERR(SEARCH("-", A1)), LEFT(TEXT(A1,"0000000"), 3) & "-" & RIGHT(TEXT(A1,"0000000"), 4), A1))
※A1の部分はそれぞれ修正したい郵便番号のセル番地を入れてください
空欄はそのまま出力
空欄はそのまま空欄で出力するため、次の数式で、最初に空欄かどうかを確認しておきます。
IF(A1 = "", "", 郵便番号を出力する処理)ハイフンありの郵便番号はそのまま出力
ハイフンが元々入っている郵便番号はそのまま出力します。次の数式で、ハイフンがあるかないかを見て、あればそのまま出力します。
IF(ISERR(SEARCH("-", A1)), ハイフンを入れる処理, A1)ハイフンなしの郵便番号は７桁までゼロ埋めしてハイフンを入れる
ハイフンなしの郵便番号は、頭のゼロが落ちて桁数が足りない可能性があるため、７桁に揃えてからハイフンを入れます。数式のこの部分で処理してます。
LEFT(TEXT(A1,"0000000"), 3) & "-" & RIGHT(TEXT(A1,"0000000"), 4)まとめ
ゼロ埋めの数式が２回出てくるあたり、プログラム的にはきれいじゃないですが、時間をかけるところでもないので、これでいいかなと思います。
今回は郵便番号のみ紹介しましたが、他にも住所の加工があって、こちらも苦労しました。
そのうちまたご紹介できればと思います。
