住所から都道府県名を分離する数式
  • Excel Version：
  • 2019
  • 2016
  • 2013
  • 2010
  • 2007
  • 2003
  • 2002
  • 2000
  • 97
  • ([2]2020-11-25)

Excel アイコン住所から都道府県名を分離する数式の解説です。

　住所の一覧を都道府県名を分離して都道府県名の付かない住所にしたい場合があります。
データ量が少ない場合には、一つずつ削除するという方法でもいいのですが、データが大量な場合は数式で処理したいところです。
この数式は、都道府県名の付いた住所から都道府県名を分離、都道府県名と市区町村以下の住所を得る数式です。

１．数式

　下表中の式、“=LEFT(TRIM(A2),IF(LEFT(……”及び“=RIGHT(TRIM(A2),LEN(TRIM(A2))-LEN(B2))”の２つをコピペしてお使いください。
　“A2” は都道府県名付の住所が入っているセル番号です。実際に住所が入っているセル番号に置き換えてご使用ください。
　また、“B2”は分離された都道府県名が入るセル番号です。実際に都道府県名が入るセル番号に置き換えてご使用ください。
　住所の前後にスペース(空白文字)が入っている場合は、削除されます。
　都道府県名が付いていない住所だった場合は、都道府県名は空白、市区町村名以下はそのまま表示されます。

A B C
1 都道府県名付住所 都道府県名 市区町村以下の住所
2 埼玉県さいたま市岩槻区加倉4-31-18 埼玉県 さいたま市岩槻区加倉4-31-18
3 (同列2行セルの式)→ =LEFT(TRIM(A2),
IF(LEFT(TRIM(A2),3)="東京都",3,0)
+IF(LEFT(TRIM(A2),3)="北海道",3,0)
+IF(OR(LEFT(TRIM(A2),3)="京都府",
LEFT(TRIM(A2),3)="大阪府"),3,0)
+IF(ISERROR(FIND("県",TRIM(A2),3)),0,
IF(OR(LEFT(TRIM(A2),4)="神奈川県",
LEFT(TRIM(A2),4)="和歌山県",
LEFT(TRIM(A2),4)="鹿児島県"),4,
IF(MID(TRIM(A2),2,3)="諸県郡",0,3)))) 		=RIGHT(TRIM(A2),
LEN(TRIM(A2))-LEN(B2))
又は
=MID(TRIM(A2),
LEN(B2)+1,LEN(TRIM(A2)))
← BACK
↑ PAGE TOP

２．数式（を組み立てられるようになりたい人のため）の解説

　この数式を作るには日本郵便(株)から公開されている郵便番号データをくまなく分析し、都道府県の文字が含まれている市区群町村名、地域名を洗い出す必要があります。
その上で都道府県を洗い出し、分離するという数式です。

2.1 都道府県名を取り出す式の解説

　 B B列セルの内容(式) 解説
2 埼玉県 =LEFT(A2,3) (1) 基本はこの式。
住所(A2)からLEFT関数を使って 3文字を取り出す式です。
つまり、都道府県名の有無からその文字数を決めることがポイントになります。
3 0 =IF(LEFT(A2,3)="東京都",3,0) (2) 最初の3文字が“東京都”か判定し、“東京都”の場合は3(字)、違う場合は0(字)とする式。
4 0 =IF(LEFT(A2,3)="北海道",3,0) (3) 最初の3文字が“北海道”か判定し、“北海道”の場合は3(字)、違う場合は0(字)とする式。
5 0 =IF(OR(LEFT(A2,3)="京都府",
LEFT(A2,3)="大阪府"),3,0) 		(4) 最初の3文字が“京都府”か“大阪府”かを判定し、どちらかの場合は3(字)、違う場合は0(字)とする式。
6 3 =IF(OR(LEFT(A2,4)="神奈川県",
LEFT(A2,4)="和歌山県",
LEFT(A2,4)="鹿児島県"),4,3) 		(5-1) 県の検出はちょっと手間がかかります。
まず、県名が4文字のチェックを考えます。
4文字の県名は“神奈川県”“和歌山県”“鹿児島県”の3県です。該当する場合は4(字)、違う場合は3(字)です。
6 3 =IF(OR(LEFT(A2,4)="神奈川県",
LEFT(A2,4)="和歌山県",
LEFT(A2,4)="鹿児島県"),4,
IF(OR(MID(A2,2,3)="諸県郡",
FIND("県",A2,3)<>3),0,3)) 		(5-2) 最初の3文字目が“県”になるケースの判定を追加します。
宮崎県東諸県郡・北諸県郡・西諸県郡で都道府県名が付いていない場合に3字目が“県”になりますので特別にチェックして 3(字)が確定、違う場合は0(字)とします。
6 3 =IF(ISERROR(FIND("県",A2,3)),0,
IF(OR(LEFT(A2,4)="神奈川県",
LEFT(A2,4)="和歌山県",
LEFT(A2,4)="鹿児島県"),4,
IF(OR(MID(A2,2,3)="諸県郡",
FIND("県",A2,3)<>3),0,3))) 		(5-3) (5-2)式は“県”の文字が含まれない場合にエラーが発生するので、“県”無しのエラーチェックを最初に追加します。エラーが発生したら 0(字)。
7 埼玉県 =LEFT(A2,(B3+B4+B5+B6)) (6) 都道府県名の文字数チェックでは、該当しない場合はすべて0(ゼロ)になるので、4つのパターンの文字数を合計して都道府県名の文字数とします。
確定した文字数の式 (B3+B4+B5+B6) でB2セルの式の文字数(3)を置き換えると、都道府県すべてに対応できるようになります。
8 埼玉県 =LEFT(A2,IF(LEFT(A2,3)="東京都",3,0)
+IF(LEFT(A2,3)="北海道",3,0)
+IF(OR(LEFT(A2,3)="京都府",
LEFT(A2,3)="大阪府"),3,0)
+IF(ISERROR(FIND("県",A2,3)),0,
IF(OR(LEFT(A2,4)="神奈川県",
LEFT(A2,4)="和歌山県",
LEFT(A2,4)="鹿児島県"),4,
IF(OR(MID(A2,2,3)="諸県郡",
FIND("県",A2,3)<>3),0,3)))) 		(7) (6)の式のB3+B4+B5+B6セル番号をそれぞれ(2)(3)(4)(5-3)の式で置き換えて、一本の式にまとめます。
9 埼玉県 =LEFT(TRIM(A2),IF(LEFT(TRIM(A2),3)="東京都",3,0)
+IF(LEFT(TRIM(A2),3)="北海道",3,0)
+IF(OR(LEFT(TRIM(A2),3)="京都府",
LEFT(TRIM(A2),3)="大阪府"),3,0)
+IF(ISERROR(FIND("県",TRIM(A2),3)),0,
IF(OR(LEFT(TRIM(A2),4)="神奈川県",
LEFT(TRIM(A2),4)="和歌山県",
LEFT(TRIM(A2),4)="鹿児島県"),4,
IF(OR(MID(TRIM(A2),2,3)="諸県郡",
FIND("県",TRIM(A2),3)<>3),0,3)))) 		(8) (7)の式では都道府県名の前にスペース(空白文字)が入っていると、正しく都道府県名を検出できないので、A2セルのデータから空白をTRIM関数を使って削除してから使うようにします。

2.2 市区町村以下を取り出す式の解説

C C列セルの内容(式) 解説
2 さいたま市岩槻区加倉4-31-18 =RIGHT(A2,17) (9)基本はこの式。住所(A2)からRIGHT関数を使って都道府県名を除いた右側の文字数分(17文字)を取り出す。
取り出す文字数を決めるのがポイントになります。
3 20 =LEN(A2) (10)LEN関数を使うと文字数を数えられる（A2セルの住所は全部で20字）。
4 さいたま市岩槻区加倉4-31-18 =RIGHT(A2,LEN(A2)-LEN(B2)) (11)住所(A2)の文字数から都道府県名(B2)の文字数を引き算すると取り出す文字数が得られるので、LEN関数でそれぞれ文字数を検出して計算する式に変更する。
5 さいたま市岩槻区加倉4-31-18 =RIGHT(TRIM(A2),LEN(TRIM(A2))-LEN(B2)) (12)(11)の式では都道府県名の前にスペース(空白文字)が入っていると、正しく都道府県名を検出できず、結果として市区町村以下の住所も正しく分離できなくなるので、A2セルのデータから空白をTRIM関数を使って削除してから使うようにします。
← BACK
↑ PAGE TOP

◆住所の都道府県名と市区町村以下を分離するのではなく、都道府県名を削除したい場合は『住所から都道府県名を削除する数式』をご覧ください。
　『コピペで使える数式：住所から都道府県名を削除する数式』(click here)

◆数式を作るにあたって裏付け調査を行いました。 (2012-8-20)
　ブログに経緯を書きましたので、興味のある方はご覧ください。
　『住所から都道府県名を削除する数式』の更新で調べたこと(click here)

← BACK
↑ PAGE TOP

Excel Tips『住所から都道府県名を分離する数式』更新記録

2020/11/25
[2] Excel2019を追加。
2017/12/09
[1] HTML5,UTF-8対応。Excel version 2016を追加。[コピー]ボタンを設置。
2012/08/29
新規Tipsとして公開
← BACK
↑ PAGE TOP
Today is