★その１：都道府県を区切る
Excel で住所を区切るのは非常に大変です。住所区切り関数そのものはありませんので色んな関数を組み合わせる必要があります。とはいえ 都道府県とそれ以下を区切るのなら簡単です。１行目にはタイトル行があるとします。
■Ａ２セル：住所
■Ｂ２セル
=LEFT(A2,3+(MID(A2,4,1)="県"))
こんな式で計算できます。都道府県は基本的にほとんどが３文字で「神奈川県」「和歌山県」「鹿児島県」だけが４文字です。また市区町村で「県」から始まるところも現在のところありませんので４文字めが「県」ならもう一文字追加して抜き取るようにすれば良いです。
★その２：市区町村を区切る (06.04.27 全面的に修正)
問題はここです。特に「区」や「市」の区切りが大変です。東京２３区や政令指定都市や単なる地区など「区」の区切りだけでも条件は非常に多いです。また市名が変わることもありますので汎用的な方法は「ない」と云っても過言ではありません。これからも新しい市町村名は生まれていくことでしょう。はっきり云ってしまえば最新の郵便番号情報をダウンロードして郵便番号から変換した方が良いと思います。
Ｅｘｃｅｌ２０１３以降なら ＦＩＬＴＥＲＸＭＬ関数で 郵便番号から住所情報をＷｅｂ検索表示することもできます。その５を参照して下さい。 (17.06.08追加)
住所分割については 色んな方が様々な方法でアプローチされてます。私のは汎用性にこだわった為に変な式にになってます。「地区」「街区」は「-」に置換しておいて下さい。
1行目タイトル行として B・C列作業列とします。
■Ａ２セル：住所
■Ｂ２セル：作業列(市区町村＋番地＋その他)
=REPLACE(A2,1,(MID(A2,4,1)="県")+3,"")
■Ｃ２セル：作業列(市の区切り位置)
=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
■Ｄ２セル：都道府県
=SUBSTITUTE(A2,B2,"")
■Ｅ２セル：市区町村
=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&"郡")))))
■Ｆ２セル：区
=IF(OR(ISERR(FIND("区",B2)),COUNTIF(A2,{"東京*区*",
"*市*町*区*","*郡*町*区*","*市*公園区*","*市*街区*","*市*地区*"}),
LEFT(B2,2)={"石狩","奥州","盛岡","南相","上越","姫路","宇陀","久遠"}),"",
REPLACE(LEFT(B2,FIND("区",B2)),1,C2,))
以上
今回は
日本郵政公社の事業所データを元にしました。
長らく更新していないので多分 もうまともに計算しないと思います
※170608最終コメント
★その３：番号を区切る
最後になりますが番号を区切ります。この前の
文字列から数字を抽出と同じ要領で区切ります。私の方法の数式に続けて
■Ｇ２セル：地名
=MID(SUBSTITUTE(B2,E2&F2,""),1,LEN(B2)-LEN(E2&F2&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,
"１","２","３","４","５","６","７","８","９","０},
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))
でもいいと思います。
これで一応完成です。Ｈ列の計算式は重いので千行単位くらいで分けて処理して下さい。
★その４：ビル・マンション名を区切る？ (変更：08.02.21)
ここまでできたものの どうしてもどうにもならないものがあります。ビル・マンション名です。ビル・マンション名はある意味
なんでもありですから 完全な区切りは おそらく不可能です。スペースで区切られているなら勿論できますが…… 多少でもましにする為 一応の数式を組みました。
■Ｉ２セル：番号
=TRIM(SUBSTITUTE(H2,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)
★その５：FILTERXML関数を使う (追加：17.06.08)
結局のところ 生き物のように変化する住所情報を 汎用的且つ半永久的に区切る方法はないのかもしれません。信用できる機関のＷｅｂ上の情報から郵便番号をキーにして住所情報を引っ張ってくるのが一番 メンテナンスの手間が少ないように思います。Ｅｘｃｅｌ２０１３以降「ＦＩＬＴＥＲＸＭＬ」関数が追加されましたので下記の数式を使って 郵便番号から住所を区切って表示させることができるようになりました。
●都道府県名
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A1,"-",""),"0000000")),"//value[5]/@*"),"")
●市区町村名
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A1,"-",""),"0000000")),"//value[6]/@*"),"")
●地名
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A1,"-",""),"0000000")),"//value[7]/@*"),"")
「value[5]」と「value[6]」だけ上記の関数で引っ張ってくれば 残りを区切るのが楽になると思います。
