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

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

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

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

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

=LEFT(JIS(セル),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS(セル)&”１２３４５６７８９”))-1)

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

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

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

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

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

「LEFT(JIS(セル),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS(セル)&”１２３４５６７８９”))-1)」式でなぜ抜き出せるのか、不思議ですが解説していきます。

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

番地の数字が何文字目にあるか計算する
計算式：FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS(セル)&”１２３４５６７８９”)
番地の直前までの文字数を計算する
計算式：MIN(FIND関数の数値)-1
住所の抽出
計算式：LEFT(住所からMIN-1)した文字分抽出

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

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

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

式のFIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS(セル)&”１２３４５６７８９”)
を検索文字列と対象に置き換えますと以下のようになります。

検索文字列：{“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”}

対象：JIS(セル)&”１２３４５６７８９”

となります。

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

番地＝数字でかつ最上位の位は１～９のいずれかが入りますので、数字を検索文字列とします。

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

そして、ここがポイントで住所に文字列で１２３４５６７８９を追加します。
数字を追加しますと、以下のようになります。
「青森県青森市緑3-9-2１２３４５６７８９」

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

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

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

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

MIN関数で算出した値に「‐１」をしていますが、番地前の住所を抜き出すためです。
青森県青森市緑3が抜き出せるので－１することで青森県青森市緑となります。

番地から前の住所を抜き出す式は
=LEFT(JIS(セル),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS(セル)&”１２３４５６７８９”))-1)
算用数字を含む住所はこの方法では抜き出せない
算用数字をターゲットにしているので、算用数字を含む場合は特殊な抽出が必要になります。
その場合はこちらのページをご覧ください。