関数だけで住所を番地前まで抜き出す方法(北海道の住所一部除く)

未分類

今回は住所を番地前まで抜き出す方法です。

↓こにような感じに住所を抜き出すことができます。

住所抜き出し結果画像

顧客データなどを分析するときにどこのエリアが多いのか番地が入っていると分析しづらい。
でも「マクロは使えない」「関数だけで抽出したい」という方向けになります。

住所を抜き出す関数はこれ

=LEFT(JIS(セル),MIN(FIND({“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”123456789”))-1)

使用する際は、セルとなっているところに該当のセルを入力し、ご利用ください。
例(D3に抜き出したい住所が入っている場合):セル → D3に書き換えます。

下記のように住所を抜き出すことが可能です。

  • 青森県青森市緑3-9-2 → 青森県青森市緑
  • 岩手県盛岡市前潟4-7-1 → 岩手県盛岡市前潟

Find関数のところで配列数式({}で閉じる)を使用していますので、式を決定する際は、Ctrl + Shift + Enterで確定します。

北海道の住所の一部は抜き出せない

注意点があり、今回の方法では北海道の一部住所を抜き出すことができません。
具体的にどういう住所が抜き出せないかというと番地以外で算用数字を使用している場合は抜き出す事ができません。例:北海道札幌市中央区北8条西○○-○○など

北海道の住所(算用数字が使われている住所)を抜き出す方法は、こちらの記事からどうぞ!

式が=LEFT(JIS(セル),MIN(FIND({“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”123456789”))-1)となる理由

「どうやってやる」の画像

「LEFT(JIS(セル),MIN(FIND({“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”123456789”))-1)」式でなぜ抜き出せるのか、不思議ですが解説していきます。

やっていることは以下の3つです。

  1. 番地の数字が何文字目にあるか計算する
    計算式:FIND({“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”123456789”)
  2. 番地の直前までの文字数を計算する
    計算式:MIN(FIND関数の数値)-1
  3. 住所の抽出
    計算式:LEFT(住所からMIN-1)した文字分抽出

使用している関数の種類

  • LEFT:指定した文字列を指定した文字数分左から抽出
  • JIS:文字列に含まれる半角を全て全角にする
  • MIN:最小値の値を返す
  • FIND:指定した文字列が最初に現れる位置を左から数え、その数値を返す

それでは実際に「青森県青森市緑3-9-2」の住所を例に住所を抜き出します。

1. 番地の数字が何文字目にあるか計算する

LEFT関数は抜き出す文字数を指定する必要があるため、青森県青森市緑3-9-2の場合、番地の直前の「緑」が何文字目になるのかを特定する必要があります。
何文字目かを判定するのが、FIND関数です。

FIND関数は(検索文字列,対象,[開始位置])となります。今回の場合3つ目の開始位置は1文字目からカウントするため、指定不要です。

式のFIND({“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”123456789”)
を検索文字列と対象に置き換えますと以下のようになります。

検索文字列:{“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”}

対象:JIS(セル)&”123456789”

となります。

検索文字列:{“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”}

検索文字列でしていることは、1~9の文字をそれぞれ検索対象の文字とします。

番地=数字でかつ最上位の位は1~9のいずれかが入りますので、数字を検索文字列とします。

対象:JIS(セル)&”123456789”

ここではまずJIS関数を使用し、住所を全角化します。
全角化する理由は、住所に半角、全角が混在している住所などがある場合があります。
そのため形式をまず統一します。

そして、ここがポイントで住所に文字列で123456789を追加します。
数字を追加しますと、以下のようになります。
「青森県青森市緑3-9-2123456789

1~9を住所に追加する理由は、FIND関数では指定した検索値が見つからなかった場合のエラーを防ぐためです。
例えば「青森県青森市緑3-9-2」の場合、2・3・9以外は情報がなくエラーとなります。それを防ぐためです。

これで検索文字列「1」~「9」が何文字目にあるのかがわかるようになります。

北海道の住所一部では今回の計算方法が使えない

北海道の一部住所では今回の方法が使用できない理由はここにあります。
北海道の住所は少し特殊で、「1条」や「1号」、「1区」など番地よりも前に算用数字を使用する住所があるためです。(例:北海道札幌市中央区北1条西11-11)

2. 番地の直前までの文字数を計算する

次にしていることは、FIND関数で算出した、1~9までの数字の中からMIN関数を使用し、最低の数字(=一番最初にヒットする数字)を計算します。
「青森県青森市緑3-9-2」の場合は、番地の3が一番最初に来る数字ですので、3が何番目にある文字かを特定します。

3. 住所の抽出

MIN関数で算出した値に「‐1」をしていますが、このままLEFT関数で抜き出

まとめ

  • 番地から前の住所を抜き出す式は
    =LEFT(JIS(セル),MIN(FIND({“1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”123456789”))-1)
  • 算用数字を含む住所はこの方法では抜き出せない
    算用数字をターゲットにしているので、算用数字を含む場合は特殊な抽出が必要になります。
    その場合はこちらのページをご覧ください。

Excelの関数は、組み合わせることでよりできることがさらに増えます。
何に使うのこの関数と思うものも中にはありますが、組み合わせて使用することですごい力を発揮します。

他にも色々とExcelの記事お作成しておりますので是非ご覧ください。

コメント