- Excel Version：
- 2019
- 2016
- 2013
- 2010
- 2007
- 2003
- 2002
- 2000
- 97
- ([3]2020-11-25)
住所から都道府県名を削除する数式の解説です。
住所の一覧を都道府県名の付かない住所にしたい場合があります。データ量が少ない場合には、一つずつ削除するという方法でもいいのですが、データが大量な場合は数式で処理したいところです。
この数式は、住所から都道府県名を除いた住所を得る数式です。
１．数式
下表中の式、“=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))),"") |
２．数式（を組み立てられるようになりたい人のため）の解説
この数式を作るには日本郵便(株)から公開されている郵便番号データをくまなく分析し、都道府県の文字が含まれている市区群町村名、地域名を洗い出す必要があります。
その上で都道府県を洗い出し、削除するという数式です。
| 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)
３．初期バージョンの数式の教訓
◆旧掲載の数式（初期バージョン）では、都道府県名以外に“都”“道”“府”“県”の文字が入っていた場合、特に都道府県名が付かいない住所だった場合には、削除されるべきではない住所部分が除かれてしまうケースが生じてしまいます。
例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『住所から都道府県名を削除する数式』更新記録
- 2020/11/25
- [3] Excel2019を追加。
- 2017/12/08
- [2] HTML5,UTF-8対応。Excel version 2016を追加。[コピー]ボタンを設置。
- 2012/08/20
- [1] 初期バージョンの不具合を修正。
- 2006/12/15
- 新規Tipsとして公開