Chiquilin Site■05.02.28_Excel:住所を区切る

その1:都道府県を区切る
その2:市区町村を区切る (修正:06.04.27)
その3:番号を区切る
その4:ビル・マンション名を区切る? (追加:05.09.29)

その1:都道府県を区切る

Excel で住所を区切るのは非常に大変です。住所区切り関数そのものはありませんので色んな関数を組み合わせる必要があります。とはいえ 都道府県とそれ以下を区切るのなら簡単です。1行目にはタイトル行があるとします。
■A2セル:住所
■B2セル
 =LEFT(A2,3+(MID(A2,4,1)="県"))
こんな式で計算できます。都道府県は基本的にほとんどが3文字で「神奈川県」「和歌山県」「鹿児島県」だけが4文字です。また市区町村で「県」から始まるところも現在のところありませんので4文字めが「県」ならもう一文字追加して抜き取るようにすれば良いです。
ページの一番上へ

その2:市区町村を区切る (06.04.27 全面的に修正)

問題はここです。特に「区」や「市」の区切りが大変です。東京23区や政令指定都市や単なる地区など「区」の区切りだけでも条件は非常に多いです。また市名が変わることもありますので汎用的な方法は「ない」と云っても過言ではありません。これからも新しい市町村名は生まれていくことでしょう。はっきり云ってしまえば最新の郵便番号情報をダウンロードして郵便番号から変換した方が良いと思います。
住所分割については 色んな方が様々な方法でアプローチされてます。私のは汎用性にこだわった為に変な式にになってます。「地区」「街区」は「-」に置換しておいて下さい。
1行目タイトル行として B・C列作業列とします。
■A2セル:住所
 
■B2セル:作業列(市区町村+番地+その他)
=REPLACE(A2,1,(MID(A2,4,1)="県")+3,"")
 
■C2セル:作業列(市の区切り位置)
=IF(MID(B2,3,1)="市",3+(MID(B2,2,2)="日市")*AND(LEFT(B2)<>
{"向","春"})+(MID(B2,3,2)="市場"),IF(MID(B2,4,1)="市",4*(COUNTIF(
A2,"東京*区*")=0)*AND(MID(B2,3,3)<>{"郡市貝","郡市川","郡市来"}),
IF(MID(B2,6,1)="市",6*AND(MID(B2,4,2)<>{"野々","郡上"})-(MID(B2,5,1)="市"),
IF(MID(B2,5,1)="市",5*(MID(B2,5,2)<>"市町")*(MID(B2,4,2)<>"郡市"),
IF(MID(B2,2,1)="市",2*(MID(B2,2,2)<>"市郡"))))))*(LEFT(B2,2)<>"関市")
+(LEFT(B2,2)="関市")*2+OR(MID(B2,6,2)={"ら市","い市"})*7
 
■D2セル:都道府県
=SUBSTITUTE(A2,B2,"")
 
■E2セル:市区町村
=LEFT(B2,IF(C2,C2,IF(OR(COUNTIF(B2,"*村*郡*"),
 COUNT(FIND("郡村",B2))),FIND("町",B2),IF(COUNT(
 FIND({"町","村","市市"},B2))+(D2="東京都"), MIN(FIND(
 {"区","町","村","市市"},B2&"区町村市市"))+COUNT(FIND(
 {"市市","町町"},B2)),FIND("郡",B2&"郡")))))
 
■F2セル:区
=IF(OR(ISERR(FIND("区",B2)),COUNTIF(A2,{"東京*区*",
"*市*町*区*","*郡*町*区*","*市*公園区*","*市*街区*","*市*地区*"}),
LEFT(B2,2)={"石狩","奥州","盛岡","南相","上越","姫路","宇陀","久遠"}),"",
REPLACE(LEFT(B2,FIND("区",B2)),1,C2,))
以上
今回は 日本郵政公社の事業所データを元にしました。
ただもし何か間違いに気付いた方は Chiquilin まで連絡下さい。直せる範囲で直します
※060407 大幅に修正。
※080321 東京都の区処理がすっぽり抜けてました^^;
※110115 ひさびさに更新。
※110911 また更新(汎用性もたせるためにやってるのに修正ばっかりやな) 
ページの一番上へ

その3:番号を区切る

最後になりますが番号を区切ります。この前の文字列から数字を抽出と同じ要領で区切ります。私の方法の数式に続けて
■G2セル:地名
=MID(SUBSTITUTE(B2,E2&F2,""),1,LEN(B2)-LEN(E2&F2&H2))
 
■H2セル:番号+その他
=REPLACE(SUBSTITUTE(SUBSTITUTE(A2,"゙",""),"゚",""),1,MIN(FIND(
JIS({1,2,3,4,5,6,7,8,9,0}),JIS(A2&1234567890)))-1,"")
 
もしくは
=LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0,
"1","2","3","4","5","6","7","8","9","0},
A2&1234567890&JIS(1234567890)))-1)

 
元データに半角カタカナが含まれてない場合は
=REPLACE(A2,1,MIN(FIND(JIS({1,2,3,4,5,6,7,8,9,0}),JIS(A2&1234567890)))-1,"")
 
量が少ないなら
=RIGHT(B2,LOOKUP(1,-LEFT(RIGHT(B2&0,COLUMN(2:2))),COLUMN(2:2)-1))
でもいいと思います。
これで一応完成です。H列の計算式は重いので千行単位くらいで分けて処理して下さい。
ページの一番上へ

その4:ビル・マンション名を区切る? (変更:08.02.21)

ここまでできたものの どうしてもどうにもならないものがあります。ビル・マンション名です。ビル・マンション名はある意味なんでもありですから 完全な区切りは おそらく不可能です。スペースで区切られているなら勿論できますが…… 多少でもましにする為 一応の数式を組みました。
■I2セル:番号
=TRIM(SUBSTITUTE(H2,J2,""))
 
■J2セル:ビル・マンションのはず
=TRIM(REPLACE(H2,1,MIN(INDEX((ISERR(-MID(H2&".",COLUMN(2:2),1))
+ISNUMBER(-MID(H2,COLUMN(2:2)+1,1))
+ISNUMBER(FIND(MID(H2,COLUMN(2:2)+1,1),"-−丁番号"))
+(MID(H2,COLUMN(2:2)+2,1)="."))*256+COLUMN(2:2),0)),""))
この数式 精度は上がりましたが非常に重いです。使う際は 一気にやらない方がいいと思います。あと 完璧とは云いがたいものですので うまくいかないものはさすがに手で修正して下さい。それでも随分ましだと思います。
→参考ファイル(012-20110911.xls)
ページの一番上へ
ChiquilinSite トップページへ  Copyright(C)2005 Chiquilin_site. All Rights Reserved.