住所から都道府県名を削除する数式

Excel Version: Excel2016 Excel2013 Excel2010 Excel2007 Excel2003 Excel2002 Excel2000 Excel97 ([2]2017-12-08)

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

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

1.数式

 下表中の式、“=REPLACE(TRIM(A2),1,IF(LEFT……”をコピペしてお使いください。
 “A2” は都道府県名付の住所が入っているセル番号です。実際に住所が入っているセル番号に置き換えてご使用ください。
 住所の前後にスペース(空白文字)が入っている場合は、削除されます。
 都道府県名が付いていない住所だった場合は、変化しません。

A B
1 都道府県名付住所 都道府県名無住所
2 埼玉県さいたま市岩槻区加倉4-31-18 さいたま市岩槻区加倉4-31-18
3 (B2セルの式)→ =REPLACE(TRIM(A2),1,
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))),"")


2.数式(を組み立てられるようになりたい人のため)の解説

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

  B B列セルの内容(式) 解説
2 さいたま市岩槻区加倉4-31-18 =REPLACE(A2,1,3,"") 基本になる式はこれです。
A2セルの“埼玉県”(都道府県名) 3字を“無”("")で置き換えます。
つまり、都道府県名の文字数を確定するのがポイントになります。
3 0 =IF(LEFT(A2,3)="東京都",3,0) (1)最初の3文字が“東京都”か判定し、“東京都”の場合は3(字)、違う場合は0(字)
4 0 =IF(LEFT(A2,3)="北海道",3,0) (2)最初の3文字が“北海道”か判定し、“北海道”の場合は3(字)、違う場合は0(字)
5 0 =IF(OR(LEFT(A2,3)="京都府",LEFT(A2,3)="大阪府"),3,0) (3)最初の3文字が“京都府”か“大阪府”かを判定し、どちらかの場合は3(字)、違う場合は0(字)
6 3 =IF(OR(LEFT(A2,4)="神奈川県",LEFT(A2,4)="和歌山県",LEFT(A2,4)="鹿児島県"),4,3)
(4-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))
(4-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)))
(4-3)“県”じゃない場合にエラーが発生するので、“県”無しのエラーチェックを最初に追加します。=0(字)
7 さいたま市岩槻区加倉4-31-18 =REPLACE(A2,1,(B3+B4+B5+B6),"") (5)都道府県名の文字数チェックでは、該当しない場合はすべて0(ゼロ)になるので、4つのパターンの文字数を合計して文字数が確定します。
確定した文字数の式 (B3+B4+B5+B6) でB2セルの式の文字数(3)を置き換えると、都道府県すべてに対応できるようになります。
8 さいたま市岩槻区加倉4-31-18 =REPLACE(A2,1,
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)))
,"")
(6)(5)の式のB3+B4+B5+B6セル番号をそれぞれ(1)(2)(3)(4-3)の式で置き換えて、一本の式にまとめます。
9 さいたま市岩槻区加倉4-31-18 =REPLACE(TRIM(A2),1,
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))),"")
(7)(6)の式では都道府県名の前にスペース(空白文字)が入っていると、正しく都道府県名を検出できないので、A2セルのデータから空白をTRIM関数を使って削除してから使うようにします。

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

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



3.初期バージョンの数式の教訓

◆旧掲載の数式(初期バージョン)では、都道府県名以外に“都”“道”“府”“県”の文字が入っていた場合、特に都道府県名が付かいない住所だった場合には、削除されるべきではない住所部分が除かれてしまうケースが生じてしまいます。
 例1:北海道寿都郡黒松内町 → 郡黒松内町 ・・・ “都”が先に判定されるため北海道が付いていても先頭~都までが除かれる。
 例2:喜多方市岩月町大都000 → 000 ・・・ 県名が付いていなくても“都”が判定されるためほぼすべてが除かれる。
 例3:東京都西多摩郡檜原村三都郷 → 西多摩郡檜原村三都郷 ・・・ 東京都がついているため県名以外の“都”があっても正しく処理される。
 例4:西多摩郡檜原村三都郷 → 郷 ・・・ 東京都であっても県名以外の“都”が判定されるため大半が除かれる。
 例5:千葉県四街道市 → 市 ・・・ 県名が付いていても“道”が先に判定されるため市より前の部分が除かれる。
 例6:山梨県甲府市 → 市 ・・・ 県名が付いていても“府”が先に判定されるため市より前の部分が除かれる。
 例7:足利市県町 → 市 ・・・ 県名が付いていないため県名ではない“県”を検出して市より前の部分が除かれる。
 例8:京都府京都市 → 府京都市 ・・・ 県名の“府”より“都”が先に判定されるため最初の京都が除かれる。
◆要注意点:安直に“都道府県”の4文字を判定に使ってしまうと、“都道府県”の文字が都道府県名以外の住所に含まれるケースが処理されておらず、正しい結果とならない場合が発生してしまいます。詰めが甘くならないようにデータをしっかり分析することが大切です。

A B
1 都道府県名付住所 都道府県名無住所
2 埼玉県さいたま市岩槻区加倉4-31-18 さいたま市岩槻区加倉4-31-18
3 (B2セルの式)→ =RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("都",A2,1))=TRUE,IF(ISERROR(FIND("道",A2,1))=TRUE,IF(ISERROR(FIND("府",A2,1))=TRUE,IF(ISERROR(FIND("県",A2,1))=TRUE,0,FIND("県",A2,1)),FIND("府",A2,1)),FIND("道",A2,1)),FIND("都",A2,1)))


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

2017/12/08
[2] HTML5,UTF-8対応。Excel version 2016を追加。[コピー]ボタンを設置。
2012/08/20
[1] 初期バージョンの不具合を修正。
2006/12/15
新規Tipsとして公開