住所から 県名 市区町村名 を分割して取り出す！

エクセルの一つのセルに入った住所データを、県名、市区町村名、それ以下に分割して取り出せないかと聞かれたので、ちょっと、考えてみました。

「長野県安曇野市豊科6000番地」 ⇒ 「長野県」「安曇野市」「豊科6000番地」

結論から言うと、全国の住所で考えた場合、エクセルの関数だけで完璧に取り出すのは、ちょっと無理なんじゃないかって気がします。僕はそれほどエクセルは詳しくはないので、もしかしたら、完璧にできるやり方があるのかもしれませんけど・・・。(^^;

それでも、大方は、取り出せるので、うまく取り出せなかったところだけ、手直ししてやれば、目的は達せられるので、そのやり方を書き留めておこうと思います。

 

都道府県名を取り出す！

ぱっと思い浮かぶのが、住所の頭から「県」を探して、あれば、そこまでを取り出し、無ければ「都」を探して、あれば、そこまでを取り出し、無ければ、「道」を探して、あれば、そこまでを取り出し、無ければ「府」を探して、それを取り出すという、IF関数の入れ子パターンです。

でも、都道府県名って、3文字か4文字なんですよね。県名に「和歌山県」とか「鹿児島県」とかの4文字がある以外は、全て3文字です。たぶん、関数としては、この文字数に注目した方が、単純になると思うので、今回は、この文字数で取り出すパターンを使いました。

上の図では、A列に住所が入っており、C列で都道府県名を取り出すようにしてあります。C1セルには、以下のような関数が入っています。

=IF(MID(A1,4,1)=”県”,LEFT(A1,4),LEFT(A1,3))

式は、もし、A1セルの4文字目が「県」だったら、A1セルの左から4文字目までを取り出し、そうでなかったら左から3文字目までを取り出すっていう意味です。都道府県名の取り出しは、この式で完璧に取り出せるんじゃないかと思います。

次にB列に、都道府県名を取り出した残りの住所が表示されるようにします。

B1セルには、以下の式が入っています。

=RIGHT(A1,LEN(A1)-LEN(C1))

式は、A1セルの文字数からC1セルの文字数を引いた数の文字数だけ右から取り出すっていう意味です。つまり、住所の文字数から都道府県名の文字数を引いた文字数だけ住所の右から数えて取り出すっていう意味ですね。

 

市区町村名を取り出す！

これが問題で、僕の中では、関数だけでは完璧な取り出し方は無いんじゃないかって思う部分です。とりあえず、以下の関数を考えてみます。

=IF(COUNTIF(B1,”*区*”),LEFT(B1,FIND(“区”,B1)),”エラー”)

都道府県名を抜いた住所のB1セルに「区」という文字があれば、B1セルの「区」という文字までの文字数を左から取り出し、「区」という文字が無ければ、「エラー」という文字を返すという意味の式です。FIND(“区”,B1)は、B1セルの中で「区」という文字が最初に現れる文字数を返しています。この数式を実際にD1セルに入れて、D10セルまでフィルハンドルを引っぱってコピーしてみると、下図のようになります。

当然の事ながら、「区」がない住所に対しては、エラーが返されます。
そこで、同じように、上の式のエラー部分に、B1セルに「市」という文字があれば、B1セルの「市」という文字までの文字数を左から取り出し、「市」という文字が無ければ、「エラー」という文字を返すという式を入れ子してやると、以下のようになります。

=IF(COUNTIF(B1,”*区*”),LEFT(B1,FIND(“区”,B1)),IF(COUNTIF(B1,”*市*”),LEFT(B1,FIND(“市”,B1)),”エラー”))

この式だと、「区」と「市」は表示されますが、当然の事ながら「町」や「村」の場合はエラーが返ってきますので、同じように、「町」と「村」も順次、エラーという文字部分に、式を入れ子してやると、以下のようになります。

=IF(COUNTIF(B1,”*区*”),LEFT(B1,FIND(“区”,B1)),IF(COUNTIF(B1,”*市*”),LEFT(B1,FIND(“市”,B1)),IF(COUNTIF(B1,”*町*”),LEFT(B1,FIND(“町”,B1)),IF(COUNTIF(B1,”*村*”),LEFT(B1,FIND(“村”,B1)),”エラー”))))

この式をD1セルに入れてD10セルまでコピーすると、以下のようになります。

見て頂くと分かる通り、四日市市や市川市など、市名に「市」の文字が使われているものは、正常に取り出せていません。もちろん、郡名や町名、村名に「市」が使われている場合も同様の事が起きます。同様に、郡名や町名、村名に「町」が使われている場合や、郡名や村名に「村」が使われている場合も、正常に取り出せません。

今回、聞かれてきた方の場合だと、住所録がほとんど長野県内のもので、上の正常に取り出せないパターンのものは、ほとんど無いようでしたので、上の式を使ってもらい、もし、正常に取り出せていないものがあった場合は、手動で修正してもらうことにしました。

しかし、住所録の中に、上の四日市市や市川市が沢山ある場合は、それでは困ってしまいます。その場合は、四日市市や市川市だけさらに、特別に上のIF関数の式の中に入れ子してやればよいと思います。

四日市市を入れ子した場合。
=IF(COUNTIF(B1,”*区*”),LEFT(B1,FIND(“区”,B1)),IF(COUNTIF(B1,”*四日市市*”),LEFT(B1,FIND(“四日市市”,B1)+3),IF(COUNTIF(B1,”*市*”),LEFT(B1,FIND(“市”,B1)),IF(COUNTIF(B1,”*町*”),LEFT(B1,FIND(“町”,B1)),IF(COUNTIF(B1,”*村*”),LEFT(B1,FIND(“村”,B1)),”エラー”)))))

ここで注意するのは、FIND(“四日市市”,B1)では、頭の「四」の文字までの数が返されてしまいます。左から取り出すのは四日市市までなので、３を足して、LEFT(B1,FIND(“四日市市”,B1)+3)としてあります。これが市川市であれば、２を足して、LEFT(B1,FIND(“市川市”,B1)+２)になるってことですね。

さらに市川市も入れ子した場合。
=IF(COUNTIF(B1,”*区*”),LEFT(B1,FIND(“区”,B1)),IF(COUNTIF(B1,”*市川市*”),LEFT(B1,FIND(“市川市”,B1)+2),IF(COUNTIF(B1,”*四日市市*”),LEFT(B1,FIND(“四日市市”,B1)+3),IF(COUNTIF(B1,”*市*”),LEFT(B1,FIND(“市”,B1)),IF(COUNTIF(B1,”*町*”),LEFT(B1,FIND(“町”,B1)),IF(COUNTIF(B1,”*村*”),LEFT(B1,FIND(“村”,B1)),”エラー”))))))

この四日市市と市川市も入れ子した式を実際にB1セルに入れてB10セルまでフィルハンドルを引っぱってコピーしてみると、下の図のように綺麗に取り出せています。

市名に「市」が使われている市は、他に野々市市とか廿日市市とかが思い浮かびますが、たぶん全国を探しても、それほど数があるわけではないと思うので、全部、上のように入れ子してやれば、市名に「市」が使われている場合も、全部、クリアできる事になります。

IF関数の入れ個数は、Excel2003以前では最大7個となっていましたが、Excel2007、Excel2010では64個になっているようなので、もし、全国の住所を調べて、郡名や町名、村名に「市」が使われているもの、郡名や町名、村名に「町」が使われているもの、郡名や村名に「村」が使われているのを、全て入れ子しても、64個にならなければ、完璧な市区町村の抜き出し関数になると思うんですけど、やはり、かなり数があるんでしょうかね・・・？

まあ、もっとも、そんな労力を費やさなくても、大方が取り出せるわけで、そこまでやる意味もないような気もしますけど・・・。(^^;

 

市区町村名以下を取り出す！

最後は簡単で、式的には以下の通りです。
=RIGHT(B1,LEN(B1)-LEN(D1))

上で、県名以下の住所を取り出した時と同じで、県名以下の住所が入っているB1セルの文字数から、市区町村名が入ったD1セルの文字数を引いた数だけ、B1セルの右側から取り出しているだけです。

 

使う関数が多少、違ったとしても、理屈としては、こんな理屈で住所から県名や市区町村名を取り出して分割するんじゃないかと思うんですけど、もしかしたら、もっと、いい考え方があるのかもしれませんね・・・。

僕は、事が足りれば、それでいいってタイプなので、完璧じゃなくても、こんなやり方で十分って思ってしまう方なんですけど・・・。(^^;

 

 

YASUKE YAMURA