Excelで住所から郵便番号を分割する方法を見つけるのは、データがあまり統一されていない場合、本当に頭の痛い作業です。住所が少しずつ異なっていたり、郵便番号が常に同じ場所に配置されているとは限りません。特に数百、数千のエントリがある場合は、手動で整理するのは現実的ではないため、非常にイライラします。しかし幸いなことに、Excel関数には、作業全体をはるかに簡単にし、面倒なコピー＆ペーストの手間を省くためのいくつかのトリックがあります。コツをつかめば、分析やメーリングリストなど、何をするにしても、データを素早く整理して整理できるようになります。
Excelで郵便番号と住所を分離する方法
方法1: RIGHTとLENを使用してPINコードの長さを均一にする
この方法は、PINコードがすべて同じ長さ、例えば6桁の場合に適しています。少し奇妙に聞こえるかもしれませんが、PINコードが常に住所の最後にある場合は、最後の6文字を切り取ってPINコードを取得し、それに応じて住所を整理することができます。
- 住所が記載された Excel シートを開きます。
- 完全な住所が格納されている列を特定します。ここでは列Aとします。
- その隣に、おそらくBに、PIN コード用の新しい列を挿入します。
- セルB2に次のように入力します
=RIGHT(A2, 6): PIN コードは常に 6 桁であると仮定して、最後の 6 文字を取得します。 - 数式を列全体にドラッグしてください。データセットによっては、最初は失敗しても、何度か試すとうまくいくことがあります。理由はよく分かりませんが、よくあることです。
さて、住所自体については、元の住所からPINコード部分を削除します。A列のA2セルに戻り、「 」と入力します。これで最後の6文字が切り取られます。これも下にドラッグするのを忘れないよう=LEFT(A2, LEN(A2)-6)にしてください。
- その後、住所と郵便番号が正しいか、いくつかの入力内容を再度確認してください。余分なスペースや、形式が少し異なると、入力内容がおかしくなることがあります。
方法2: FINDを使用して可変のPINコード位置を持つアドレスを処理する
PINコードが必ずしも末尾にない場合や長さが異なる場合、この方法の方が柔軟です。少し複雑ですが、乱雑なデータでもうまく機能します。
- PINコードの先頭に必ずスペースまたは区切り文字が付いているとします。FIND関数を使用して、その区切り文字を見つけます。例:
=FIND(" ", A2). - これは最初のスペースの位置を示します。PINコードが末尾にあり、その前にスペースが1つしかない場合は、これでPINコードの開始位置を特定できます。
- 次に、スペースの位置を指定してRIGHTキーを
=TRIM(RIGHT(A2, LEN(A2) - FIND(" ", A2)))押し、PINコードを取得します。これにより、スペース以降のすべての内容が抽出されます。 - アドレスの場合、
=LEFT(A2, FIND(" ", A2) - 1)スペースの前のすべてを取得するには: を使用するとよいでしょう。
この設定は、住所が一貫していない場合には少し脆弱になりますが、PIN コードが最後に表示され、スペースで区切られている場合には適切に適用されます。
追加のヒント
データが完璧ではない場合があります。余分なスペース、異なる形式、またはPINコードの欠落などにより、処理が複雑になる場合があります。これらの方法を適用する前に、 TRIM関数を使用すると余分なスペースを除去できます。また、何か問題が発生した場合に備えて、作業を開始する前に必ずバックアップを作成してください。大量のデータを扱う場合は、マクロや自動化スクリプトを使用すると、時間と手間を大幅に節約できます。
正直なところ、設定によっては、これらの数式が最初から完璧に動作するとは限りません。いくつかの不整合を修正した後、更新または再適用が必要になる場合があります。少し面倒ですが、慣れれば対処できます。
まとめ
- Excelファイルを開く
- 住所列を特定する
- PINコード用の新しい列を挿入する
- RIGHT、LEFT、LEN、およびオプションでFIND関数を使用してデータを分割します
- 数式を下にドラッグしてデータセット全体をカバーします
- エラーや乱雑なデータを確認して修正する
まとめ
Excelで郵便番号と住所を分離するのはそれほど難しいことではありませんが、データの形式が完璧でない場合は少し面倒です。重要なのは、データの一貫性、つまり郵便番号が各住所の末尾にきちんと配置されているか、それともどこか別の場所に埋もれているかに基づいて適切な方法を選択することです。これらの機能をマスターすれば、特に何千ものエントリをクリーンアップする必要がある場合、面倒な作業があっという間に完了します。ファイルのバックアップと作業内容の二重チェックを忘れないでください。この方法が誰かの時間を節約してくれることを願っています。私は複数のデータセットでこの方法でうまくいきましたので、皆さんにも同じように効果があることを願っています。