【無料DL】Excelの住所を都道府県名で分割・丁目で分割するワザ

この記事の所要時間: 455

こんにちは。京野です。

年賀状のシーズンになると、住所録にある住所の整理が必要になってきます。
スマホに入っている住所とかを抜き出して名寄せ・マージしたりとかしますよね。

そういうときに困るのが、
「住所1」とか「住所2」「住所3」といったように、住所が分割していること。
いや、分割しているのはいいんですけど、その分類の仕方が住所録によって違っているんですよね。

そういうのは結構面倒です。

なので、私はそういう場合、Excelに名簿を落としたら、まずは1つにしたうえで、その後再度分割するという方法をとることで、同じ基準にしてから保存するようにしています。

最近、知人からクロネコヤマトの「B2」という送り状発行ソフトで使えるようにエクセルのデータを整えたい、という要望があり、名簿をいじる機会があったので、このまとめ方を備忘録的に残そうと思います。

最後にサンプルファイルを無料でDLできるリンクがあります。

レッツゴー・ウンチキスト!

まず、住所をひとまとめにする方法

例えば、下記のようなデータがあるとします。
下記のデータは、すべて日本に存在する郵便番号と、町名ですが、その後の
3-1という町名は本当に存在するかわかりません。

ランダムに発生させたので、その辺はご了承ください。

さて、下記のような情報がセルに分かれていた場合(/はセルが分かれることを示す)

824-0014/福岡県/行橋市長井/3-1-1F

郵便番号は独立したままでいいですけど、その後の3つは一つにしたいですよね。
これは「&」マークを使えば一つのセルに合併できます。

=B1&C1&D1

その合わせたセルに表示されているのは計算結果なので、
文字列として表示するには、一度コピーして値として貼り付けましょう。

さて、このように住所が頭から下までひとつになったデータを、分割する方法を下記、書いていきます。

3つの方法で書きますので、必要なものを使ってください。

都道府県のところでセルを分ける(都道府県+それ以下)で分ける【2分割】

これは一番簡単です。基本3文字目までが都道府県で、例外は
「神奈川県」「和歌山県」「鹿児島県」なので。
4文字目が「県」だったら、4文字目まで、もしそれ以外だったら3文字目までという数式を使います。
C2のセルは、A2の元の住所のセルの文字数から、B2の都道府県名のセル文字数を引いて、その文字数分、A2のセルの右から文字を抜き出す式です。

 

A2のセルに、元の住所が入っているとすると、B2とC2はそれぞれ…

B2のセル
=IF(MID(A2,4,1)=”県”,LEFT(A2,4),LEFT(A2,3))

C2のセル
=RIGHT(A2,LEN(A2)-LEN(B2))

都道府県では分けずに、丁目の数値の前で分ける【2分割】

丁目の前で分ける方法はこの方法で可能でした。

A2のセルに、元の住所が入っているとすると、B2とC2はそれぞれ…

B2のセル
=LEFT(A2,MATCH(FALSE,INDEX(ISERROR(MID(A2,ROW($A$1:$A$256),1)*1),,),0)-1)

C2のセル
=RIGHT(A2,LEN(A2)-LEN(B2))

都道府県名と、丁目の数値の前と両方で分ける【3分割】

A2のセルに、元の住所が入っているとすると、B2とC2とD2はそれぞれ…

B2のセル
=IF(MID(A2,4,1)=”県”,LEFT(A2,4),LEFT(A2,3))

C2のセル
=RIGHT(LEFT(A2,MATCH(FALSE,INDEX(ISERROR(MID(A2,ROW($A$1:$A$256),1)*1),,),0)-1),LEN(LEFT(A2,MATCH(FALSE,INDEX(ISERROR(MID(A2,ROW($A$1:$A$256),1)*1),,),0)-1))-LEN(B2))

D2のセル
=RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2))

となります。力技で作ったので、C2のセルがちょっと長いですけど…。

少しだけ既知の問題点

この方法で、ほとんど問題なくできると思いますが、実際10万ほどの住所でやってみたところ、問題が見つかりました。

この「丁目」の前で分ける分割は、「数字」の前で分割する方法です。
なので、住所に数値が入っていると、そこで分割されます。

たとえば、このエクセルシート(ダウンロードできます)の、478番には

北海道旭川市7条通1丁目4-1-4F

という住所があります。
この旭川市の7条通という住所は、漢数字の七条通ではなく、7条通が正しいようです。(郵便局のサイトによると1条から9条まであるようです)

 

こういう、住所に数値が入っている場合は、そこから分割されてしまいます。

都道府県名/市区町村名/丁目以下
【理想】 北海道/旭川市7条通/1丁目4-1-4F
【実際】 北海道/旭川市/7条通1丁目4-1-4F

おまけ:【無料DL】Excelサンプルファイルのダウンロード

サンプルファイルが下記からDLできます。

シートは3枚あります。

http://bit.ly/2jbCEXC

address.xlsxというファイルです。DLは無料です。

そのまま住所だけ替えて使ってください。Aの水色の列のデータを消して、ご自身の分割したいデータを貼って入れてください。(黄色の列には数式が入っています)

※住所ファイルは都道府県名必須です。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です