郵便番号と住所が混在しているデータをExcelで住所のみを抜き出す方法は?それぞれ分ける方法は?

未分類

今回は郵便番号と住所が混在しているデータをExcelで住所のみを抜き出す方法・それぞれ分ける方法です。

例えば営業箇所を作る時など、Googleマップを使う方いると思いますが、Googleマップの住所の多くは郵便番号と住所が混在している形になっています。

Googleマップの現状

これをExcelで入力すると郵便番号と住所が混在したデータになるので、今回は以下のように抽出とそれぞれ分けたいと思います。

今回できること

気づかれたと思いますが、抽出方法もそれぞれ分ける方法も同じ方法になります。
これを覚えてしまえば、どこを起点にして抜き出すかだけなので、様々なところで役立つ方法です。

注意点

Googleマップには、郵便番号を含んだ住所と含まない住所があります。
今回は郵便番号が入っていないものは、空欄にし住所のみを抜き出す。という方法にしたいと思います。

郵便番号・住所をそれぞれ抽出する方法

郵便番号の抽出方法

郵便番号の抽出で必要なのは、抽出となるを見つけるFind関数左側の文字列を抜き出すLeft関数を使います。

式は以下の式です。

=LEFT([@住所],FIND(” “,[@住所])-1)

エラー処理を含んだ式は、エラー処理のところで記載します。

解説

先も述べたとおり、郵便番号を抜き出すポイントは抜き出すための軸と文字数を求める必要があります。
熊谷駅の住所を例にしますと、〒360-0037 埼玉県熊谷市筑波2丁目115となっています。

この住所の法則性を見つけます。

  • 郵便番号は全部で9文字
  • 郵便番号の後には必ず半角スペースが一つ入る

この2つの法則性が見つかります。
今回は、郵便番号は左側を、住所は右側を抜き出すのに、半角スペースを起点に抜き出しますので、郵便番号の後には必ず半角スペースが入る方で抽出します。

半角スペースが起点になりますので、FIND関数を使用し、半角スペースが何文字目にあるか数えます。
FIND関数は、(検索文字列,対象,[開始位置])の情報が必要になります。※[開始位置]は任意で、今回は不要のため使用しません。

FIND(” “,[@住所])

これで、半角スペースの位置がわかったと同時にLEFT関数で抽出する文字数がわかりました。
しかし、LEFT関数で抜き出す文字数はFIND関数で求めた数値ー1にします。
その理由が以下の図です。

郵便番号抽出のイメージ

FIND関数では半角スペースのある文字数を求めます。なので10という数値になってしまうため、-1をしています。
以上が、郵便番号抜き出しの方法です。

住所の抽出方法

郵便番号の抽出ができれば住所の抜き出しも簡単で、郵便番号を引いてあげれば住所のみになります。

式は以下の式です。

=RIGHT([@住所],LEN([@住所])-FIND(” “,[@住所]))

RIGHT関数は(文字列,文字数)でLEFT関数と仕組みは同じで、抜き出す方向が異なります。
FIND関数は左から文字数をカウントするため、FIND関数だけではうまく文字数を抜き出すことができません。
そこで、全体文字数-半角スペースの位置(郵便番号)という式にします。

住所抽出のイメージ


全体の文字数を測定する関数はLEN関数を使います。FIND関数を引く事で抜き出すべき文字数がわかります。

その結果、式はこのようになります。

=RIGHT([@住所],LEN([@住所])-FIND(” “,[@住所]))

以上が住所を抜き出す方法です。

郵便番号がない場合の対応(エラー処理)

今回、郵便番号がない場合の対応が必要です。
郵便番号がないという事は、起点となる半角スペースがないことになります。

この場合、Find関数では見つけるデータがないため、#VALUE!エラーになります。
郵便番号は空欄、住所はそのまま表示にします。
それぞれの式は以下の通りです。

郵便番号抜き出し:

=IFERROR(LEFT([@住所],FIND(” “,[@住所])-1),””)

住所抜き出し:

=IFERROR(RIGHT([@住所],LEN([@住所])-FIND(” “,[@住所])),[@住所])

IFERRORを使う際の注意点

その答えが本当にエラーなのかどうかわかった上で利用する必要があります。
例えば関数名が違ったりすると表示される#Name?エラーがありますが、これもIFERROR関数を使うと、今回のケースであれば空欄処理をします。
エラーに気づかないという事になりますので、IFERROR関数を利用する際はご注意ください。

まとめ

今回は、郵便番号と住所が混在しているデータをExcelで住所のみを抜き出す方法・それぞれ分ける方法をお届けしました。

今回半角スペースを基に文字を抜き出しています。
この方法は名前を姓と名を分ける方法にも応用することができます。
ぜひ覚えて活用してみてください。

他にも下記のように関数を複数使ったテクニック集があります。
ぜひご覧ください。

コメント