Excelで住所を分ける関数が間違える市区町村｜「市」で切る式の落とし穴

Excelで住所を分けるとき、都道府県から書き始めた住所なら、都道府県は関数で47すべて正しく分けられます。 崩れやすいのは市区町村で、「市」「町」などの字の位置で切る式は、名前の途中に同じ字を含む市町村で切れてしまいます。

この記事では、総務省の市区町村の一覧（令和6年1月1日更新、1,747の市区町村）を使って、どの名前で崩れるかを数えました。 崩れる形と、崩れた行を見つける方法を順に説明します。

都道府県は「4文字目が県か」で47すべて分けられる

都道府県の名前は、ほとんどが3文字です。 4文字なのは神奈川県・和歌山県・鹿児島県の3つだけで、どれも4文字目が「県」です。

そこで、よく使われるのが次の式です。 A2に住所が入っているとします。

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

4文字目が「県」なら4文字、そうでなければ3文字を取り出します。 3文字の都道府県のあとに「県」で始まる市区町村は無いので、住所が都道府県から始まっていれば47すべてで正しく分かれます。

市区町村を字の位置で切ると、24の名前で崩れる

市区町村を取り出す式は、都道府県を除いた残りから「市」「区」「町」「村」の位置を探して切る形です。 ここでは、FINDでそれぞれの位置を探し、いちばん前の位置までをLEFTで取り出す方法で数えます。

探す順番を決めて入れ子にする式では、崩れる名前の顔ぶれが変わります。 たとえば「市」から先に探す式なら、町田市は正しく切れます。

この方法は、名前の最後の字より前に「市」「区」「町」「村」を含む名前で崩れます。 総務省の一覧で数えると、そうした市町村は24ありました。

• 頭が「市」: 市川市・市原市・市貝町・市川町・市川三郷町
• 途中に「市」: 余市町・上市町・下市町・四日市市・廿日市市・野々市市
• 途中に「町」: 十日町市・町田市・大町市・大町町
• 途中に「村」: 村田町・村山市・村上市・東村山市・武蔵村山市・羽村市・田村市・玉村町・大村市

たとえば「千葉県市川市八幡」は、残りの「市川市八幡」の1文字目で切れて「市」になります。 「佐賀県杵島郡大町町」は「杵島郡大町」で切れ、最後の「町」が残りの側に回ります。

「郡」も探すと、さらに6つ増える

郡の付いた住所に合わせて「郡」も探すと、名前に「郡」を含む市町村が崩れます。 郡山市・郡上市・蒲郡市・上郡町・大和郡山市・小郡市の6つです。

先の24と合わせると、名前の途中に「市」「区」「町」「村」「郡」のどれかを含む市町村は30になります。 探す字を増やすほど、崩れる名前も増えます。

郡まで書いた住所は、郡の名前でも崩れる

郡を書いた住所では、郡の名前にも同じ字が入っていることがあります。 郡の名前に「市」か「村」を含むのは、余市郡・高市郡・田村郡・東村山郡・西村山郡・北村山郡の6つです。

この6つの郡の町村を郡から書くと、市町村の名前に問題が無くても途中で切れます。 「山形県西村山郡河北町」なら、「西村」で切れてしまいます。

郡から書いた形で数えると、崩れる組み合わせは22ありました。 郵便番号データで郡の付く町村は917あり、住所録では郡を書く人と書かない人が混ざりがちです。

政令指定都市の区は「市」で止まる

「神奈川県横浜市中区本町」のような住所は、いちばん前の「市」で切れて「横浜市」になります。 区の名前は、残りの「中区本町」の側に入ります。

一覧には政令指定都市が20、その区が171あり、どの区もこの形になります。 市までで集計したいなら、このままで困りません。

区まで1つの列に入れたいときは、残りの側からもう一度「区」で切る手順が要ります。 東京都の千代田区のような特別区は、それ自体が市区町村なので、最初の「区」で正しく切れます。

都道府県の無い住所は、25の名前で都道府県を決められない

市から書かれた住所に都道府県を補うときは、市区町村の名前から都道府県を引くことになります。 ところが、同じ名前の市町村が2つ以上の都道府県にあるものが25あります。

• 府中市: 東京都・広島県
• 伊達市: 北海道・福島県
• 池田町: 北海道・福井県・長野県・岐阜県

この25の名前は、名前だけでは都道府県を1つに決められません。 郡まで書いてあれば決まることもあり、「安芸郡府中町」なら広島県です。

区の名前から書いた住所も同じです。 東京都の特別区のうち北区・中央区・港区の3つは、大阪市北区のように政令指定都市の区にも同じ名前があります。

崩れた行を見つける方法

関数で分けたあとは、切り出した都道府県と市区町村の組が一覧に載っているかを確かめると、崩れた行が見つかります。 総務省の「全国地方公共団体コード」のページからExcelファイルを取り、別のシートに貼っておきます。

一覧のシートには、都道府県名と市区町村名をつないだ列を1つ足します。 切り出した側でも都道府県と市区町村をつなぎ、COUNTIFでその列を数えます。 数が0の行は、一覧に無い組で切れた行です。

市区町村の名前だけで照らすと、崩れた行を見逃すことがあります。 東村山市を字の位置で切ると「東村」になりますが、東村は沖縄県にある村なので、名前だけなら一覧に載っています。

行数が多くて手で直しきれないときは、住所分割ツールに住所の列を貼る方法もあります。 字の位置ではなく一覧の名前と照合して分け、補った行や一覧に無い行を確認メモに書いて返します。

まとめ

都道府県は「4文字目が県か」の式で、都道府県から書き始めた住所なら47すべて分けられます。 市区町村を字の位置で切る式は、24の市町村と、郡まで書いた22の組み合わせで途中で切れます。

分けたあとは、都道府県と市区町村の組を一覧と照らすと、崩れた行を拾えます。 操作の流れは「住所を都道府県・市区町村・番地以降に分ける手順」にまとめました。