事務ツール

Excel で住所を都道府県と市区町村に分ける式と、式では切り間違える住所

住所録の住所が 1 つのセルに入っていると、都道府県ごとに並べ替えたり、宛名ソフトの列に合わせたりできません。都道府県は Excel の式で確実に分けられますが、市区町村は式だけでは切り間違える住所があります。

都道府県を取り出す式

A2 に住所があるとき、B2 に次の式を入れます。

=IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))

都道府県の名前は、神奈川県・和歌山県・鹿児島県の 3 つだけが 4 文字で、ほかはすべて 3 文字です（北海道・東京都・大阪府・京都府も 3 文字）。そこで、4 文字目が「県」なら 4 文字、そうでなければ 3 文字を取り出します。

この式は、住所が都道府県から書かれていることが前提です。「千代田区…」のように都道府県が省かれた住所や、先頭に郵便番号や空白がある住所では、正しく取り出せません。

都道府県より後ろを取り出す式

C2 に次の式を入れると、B2 の都道府県を除いた残りが取り出せます。

=MID(A2,LEN(B2)+1,LEN(A2))

市区町村を分ける式と、その限界

市区町村は、残りの住所で最初に出てくる「市・区・町・村」までを取り出す式がよく使われます。

=LEFT(C2,MIN(FIND({"市","区","町","村"},C2&"市区町村")))

多くの住所はこれで分けられますが、名前の途中に「市・区・町・村」が入っている市区町村では、その位置で切ってしまいます。日本郵便の郵便番号データで数えると、次の 15 の市がそうです（政令指定都市の区と郡の町村を除く）。

たとえば「四日市市諏訪町」は「四日市」、「町田市森野」は「町」、「東村山市本町」は「東村」で切れます。このほかにも、次のような住所は式では思ったとおりに分けられません。

町域（町名）と番地、建物名をさらに分けるのは、式ではもっと難しくなります。町域の名前に漢数字が入る（「北一条西」など）、番地の書き方がそろっていない（「1-2-3」と「一丁目2番3号」）といった違いがあるためです。

確実に分けるには

市区町村や町域を確実に分けるには、文字の位置ではなく、実在する市区町村と町域の名前と照らし合わせる必要があります。このサイトの住所の分割は、日本郵便の郵便番号データにある名前と照らし合わせて、都道府県・市区町村・町域・番地・建物名に分けます。上の切り間違える市や郡の町村もそのまま分けられ、都道府県が書かれていない住所は市区町村から補います。郵便番号データの町域から作った約 1 万 7 千件の住所で、すべて正しく分けられることを確かめています。

住所録の CSV を読み込めば、分けた列を書き足した CSV を保存できます。読み込んだ住所録はどこにも送信しません。

分けたあとに

参考にした資料