住所から都道府県とそれ以外の部分を取得する(COUNTIF,IF,LEFT,RIGHT,LEN,OR)

住所のデータがまとめて入力されたセルから都道府県の部分とそれ以外の部分をそれぞれ分けて取得する方法です。ここでは Excel の関数である COUNTIF 関数、 IF 関数、 LEFT 関数、 RIGHT 関数、 LEN 関数、 OR 関数を使って住所から都道府県とそれ以外の部分を取得する方法について解説します。

(2021 年 03 月 07 日公開 / 2022 年 03 月 07 日更新)

住所から都道府県とそれ以外を取得する

住所が入力されたデータから都道府県とそれ以外の部分をそれぞれ取得します。なお前提として各データは都道府県から入力されていると仮定しており、都道府県がない場合はチェックしていません。次のシートを見て下さい。

住所から都道府県とそれ以外を取得する(1)

最初に都道府県の部分を取り出します。都道府県で 4 文字なのは神奈川県、和歌山県、鹿児島県の 3 県だけなので、 IF 関数と COUNTIF 関数を使って「神奈川県」「和歌山県」「鹿児島県」のいずれかで始まっていれば先頭から 4 文字、それ以外の場合は先頭から 3 文字の文字列を取得します。 C3 セルに次のように数式を入力しました。

=IF(OR(COUNTIF(B3,"=神奈川県*")>0,COUNTIF(B3,"=和歌山県*")>0,COUNTIF(B3,"=鹿児島県*")>0),LEFT(B3,4),LEFT(B3,3))

住所から都道府県とそれ以外を取得する(2)

Enter キーを押すと C3 セルには次のように表示されます。

住所から都道府県とそれ以外を取得する(3)

C4 セルとから C9 セルにも同じ数式を入力しました。

住所から都道府県とそれ以外を取得する(4)

次に都道府県以外の部分を RIGHT 関数を使って取り出します。 IF 関数と COUNTIF 関数を使って神奈川県、和歌山県、鹿児島県のいずれかで始まっていれば、取得する文字数は文字列の数から 4 を減算した数、それ以外の場合は文字列の数から 3 を減算した数となります。 D3 セルに次のように数式を入力しました。

=IF(OR(COUNTIF(B3,"=神奈川県*")>0,COUNTIF(B3,"=和歌山県*")>0,COUNTIF(B3,"=鹿児島県*")>0),RIGHT(B3,LEN(B3)-4),RIGHT(B3,LEN(B3)-3))

住所から都道府県とそれ以外を取得する(5)

Enter キーを押すと D3 セルには次のように表示されます。

住所から都道府県とそれ以外を取得する(6)

D4 セルとから D9 セルにも同じ数式を入力しました。

住所から都道府県とそれ以外を取得する(7)

対象の文字列から都道府県の部分とそれ以外の部分の文字列をそれぞれ抽出することができました。

解説

例えば対象のデータの "東京都" から始まっているかどうかは COUNTIF(B3,"=東京都*")>0 という数式を使って確認しています。この場合 B3 セルの文字列が "東京都" から始まっていれば 1 、そうでなければ 0 を返します。

都道府県の中で 4 文字なのは神奈川県、和歌山県、鹿児島県の 3 県だけなので OR 関数を使っていずれか一つに一致するかどうかを調べます。

OR(COUNTIF(B3,"=神奈川県*")>0,COUNTIF(B3,"=和歌山県*")>0,COUNTIF(B3,"=鹿児島県*")>0)

いずれかに一致すれば 4 文字、いずれにも一致しない場合は 3 文字分を先頭から取得します。

=IF(・・・,LEFT(B3,4),LEFT(B3,3))

都道府県以外の部分も基本的には同じですが、 LEN(B3) でデータの文字数を取得し、 3 文字または 4 文字分だけ減算した数の文字列を今度は末尾から取得します。

=IF(・・・,RIGHT(B3,LEN(B3)-4),RIGHT(B3,LEN(B3)-3))

このようにして県か県でないかで分けて、都道府県の部分と都道府県以外の部分をそれぞれ取得しっます。

今回使用した関数の解説は下記を参照されてください。

-- --

Excel の関数である COUNTIF 関数、 IF 関数、 LEFT 関数、 RIGHT 関数、 LEN 関数、 OR 関数を使って住所から都道府県とそれ以外の部分を取得する方法について解説しました。

( Written by Tatsuo Ikura )

広告
Profile
profile_img

著者 / TATSUO IKURA

初心者~中級者の方を対象としたプログラミング方法や開発環境の構築の解説を行うサイトの運営を行っています。