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

こんにちは。京野です。

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

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

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

なので、私はそういう場合、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のセルがちょっと長いですけど…。