Home>PCワンポイント活用術>エクセル(Excel)の活用術>コピペで使える数式【住所から都道府県名を削除する数式】

住所から都道府県名を削除する数式
Excel Version: Excel2007 Excel2003 Excel2002 Excel2000 Excel97 Excel95
 この数式は、都道府県名の付いた住所から都道府県名を削除した住所を得る数式です。

● 数式
 下表中の式、“=RIGHT(A2,LEN(……”をコピペしてお使いください。
 “A2”は都道府県名付の住所が入っているセル番号です。実際に住所が入っているセル番号に置き換えてご使用ください。
  A B
1 都道府県名付住所 都道府県名無住所
2 埼玉県さいたま市岩槻区加倉4-31-18 さいたま市岩槻区加倉4-31-18
  (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)))

● 数式を組み立てられるようになりたい人のための解説
  B B列の式 解説
2 さいたま市岩槻区加倉4-31-18 =RIGHT(A2,17) 基本になる式はこれ。
A2セルの内容からRIGHT関数を使って右側17文字分を取出す。
3 さいたま市岩槻区加倉4-31-18 =RIGHT(A2,20-3) “17”文字は、A2セル住所の文字数(20)から“埼玉県”の文字数(3)を引いて求めることにします。
4 さいたま市岩槻区加倉4-31-18 =RIGHT(A2,LEN(A2)-FIND("県",A2,1)) 住所の文字数(20)はLEN関数を使って計算、埼玉県の文字数(3)はFIND関数を使って“県”の文字位置を求めることで置き換えます。
5 #VALUE! =RIGHT(A2,LEN(A2)-FIND("道",A2,1)) 試しにFIND関数の"県"を"道"に置き換えてみます。
当然ですが、A2セルの住所には“道”の文字が無いのでエラー(#VALUE!)になります。
6 #VALUE! =FIND("道",A2,1) FIND関数の部分だけを取出した式にしてみると良く分かります。
7 3 =FIND("県",A2,1) “県”にするとFIND関数の結果がちゃんと出ます(3)。
8 TRUE =ISERROR(B6) エラーを発生したFIND関数の実験式セル(B6)をISERROR関数を使ってチェックしてみましょう。
エラーが発生しているので“TRUE”(正しい=エラー有)が出ます。
9 TRUE =ISERROR(FIND("道",A2,1)) ISERROR関数のセル番号(B6)の部分をB6セルのFIND関数の式で置き換えます。
10 3 =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)) ISERROR関数とIF関数を組み合わせて都道府県を全部チェックする式に展開します。
都道府県名が付いていない住所にも対応するため、すべてをIF関数でチェックし、該当するものが無い場合には“0”を出すようにします(RIGHT関数で計算するときに取出す文字数を全部にするとエラーは出さないで済みます)。
IF関数は入れ子構造になります。チェックの時の都→道→府→県の順番が計算結果を出すときには県→府→道→都の順になりますので注意してください。
11 さいたま市岩槻区加倉4-31-18 =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))) 完成した式でB4セルの式の“FIND("県",A2,1)”部分を置き換えます。



Copyright© Ufuidotnet, 2003〜2012, all rights reserved. Excel住所から都道府県名を削除する数式counter