エクセルに入力した住所を都道府県•市区町村•それ以降の3つに分ける関数です。
「市区町村」は日本郵政のサイトから住所の郵便番号（CSV型式）をダウンロードして、重複なく一覧化したものをベースにしています。
住所を分割するエクセル関数
B列に住所を入力（または貼り付け）するとC〜E列に都道府県•市区町村•それ以降に分かれた住所が表示されます。F列より右は処理用の関数です。
2行目にある項目名称も関数に関わっているので、以下の通りに入力する必要があります。
| 対象セル | 名称 |
|---|---|
| B2〜F2 | （指定なし） |
| G2 | 市 |
| H2 | 区 |
| I2 | 郡 |
| J2 | 町 |
| K2 | 村 |
| L2 | （指定なし） |
結果表示用の関数【C3～E3】
処理用関数のデータなどを基に、都道府県•市区町村•それ以降の住所をここに反映します。
=IF(MID($B3,4,1)="県",LEFT($B3,4),LEFT($B3,3))=IF(COUNTIF($L:$L,$G3)=1,$G3,IF(COUNTIF($L:$L,$H3)=1,$H3,IF(COUNTIF($L:$L,$I3)=1,$I3,IF(COUNTIF($L:$L,$J3)=1,$J3,IF(COUNTIF($L:$L,$K3)=1,$K3)))))=SUBSTITUTE($B3,$C3&$D3,"")処理用の関数【F3～K3】
住所を3つに分けるための下準備として、都道府県以外の住所•市まで•区まで•郡まで•町まで•村までの住所を出しています。
=SUBSTITUTE($B3,$C3,"")=IFERROR(IF(LEFT($F3,1)="市",LEFT($F3,3),IF(OR(LEFT($F3,4)="四日市市",LEFT($F3,4)="野々市市",LEFT($F3,4)="廿日市市"),LEFT($F3,4),LEFT($F3,FIND(G$2,$F3)))),"-")=IFERROR(LEFT($F3,FIND(H$2,$F3)),"-")=IFERROR(LEFT($F3,FIND(I$2,$F3)),"-")=IFERROR(IF(MID($F3,4,3)="大町町",LEFT($F3,6),LEFT($F3,FIND(J$2,$F3))),"-")=IFERROR(LEFT($F3,FIND(K$2,$F3)),"-")市区町村一覧【L3】
L列は関数ではなく市区町村の一覧です。日本郵政のサイトから住所の郵便番号（CSV型式）をダウンロードし、市区町村の列をピポットグラフを利用して重複分を除いた一覧にしています。
この列に重複した市区町村があると正しく機能しない仕組みにしています。
関数の説明
使用した主な関数についての概要です。
F3【都道府県以外】
=SUBSTITUTE($B3,$C3,"")住所の中に含まれる都道府県の名前を”文字無し”に置き換える。※「削除」と同等
G3【市までの住所】
=IFERROR(IF(LEFT($F3,1)="市",LEFT($F3,3),IF(OR(LEFT($F3,4)="四日市市",LEFT($F3,4)="野々市市",LEFT($F3,4)="廿日市市"),LEFT($F3,4),LEFT($F3,FIND(G$2,$F3)))),"-")都道府県を除いた住所（F3）の左からスタートして最初の「市」までの文字を抜き出す、がベースになっています。しかし、市の名称の中に「市」が2つ含まれているエリアがあるので、正しい地名が拾えるように調整しています。
- 千葉県市川市（いちかわし）
- 千葉県市原市（いちはらし）
- 愛知県四日市市（よっかいちし）
- 石川県野々市市（ののいちし）
- 広島県廿日市市（はつかいちし）
「市」から始まる市は市川市と市原市だけなので、都道府県以外の住所の最初の文字が「市」なら左から3文字を抜き出す。それに当てはまらない場合は都道府県以外の住所の最初の4文字が四日市市•野々市市•廿日市市なら左から4文字を抜き出す。
3〜4文字目が「市市」という条件で関数を少なくしようとしましたが、「◯◯市 市◯町」のように市から始まる町名が多数あったので、個別に調整しています。
H3【区までの住所】
=IFERROR(LEFT($F3,FIND(H$2,$F3)),"-")都道府県を除いた住所（F3）の左からスタートして最初の「区」までの文字を抜き出しています。
他の市区町村の関数も2行目の文字を探しに行かせてますが、以下のように「＂区＂」に置き換えても使用できます。
=IFERROR(LEFT($F3,FIND("区",$F3)),"-")I3【郡までの住所】
=IFERROR(LEFT($F3,FIND(I$2,$F3)),"-")郵政の一覧を見る限り「郡」で終わる市区町村名はなく、◯◯郡◯◯町というような構成が殆どでした。ただ、何かしらニーズがあると思い追加しています。
J3【町までの住所】
=IFERROR(IF(MID($F3,4,3)="大町町",LEFT($F3,6),LEFT($F3,FIND(J$2,$F3))),"-")町名に「町」が2つ含まれているのが大町町（おおまちちょう）です。左から4文字目をスタート地点としてそこからの3文字が「大町町」の場合、左から6文字までを返す、という関数です。
Comment