「StiLL」Excel情報209　関数--住所を地名と番地に分けて表示②

■前回に引き続き、住所を地名と番地に分ける方法をご紹介します。今回は前回の方法を応用して、1つの数式内に全ての検索値を入れて、数字の位置の値を抽出する方法です。

☆検索値の数字を全て {　} で囲み、FIND関数に検索文字列として入れ、MIN関数で囲んで数字の位置を抽出します。その値からLEFT関数で地名を、SUBSTITUTE関数で番地をそれぞれ出します。

※ご注意下さい。半角数字と全角数字が混在している場合は、番地を半角数字か全角数字のどちらかに統一する必要があります。

■住所一覧を地名と番地に分ける

前回は、住所の中の数字が始まる位置の

値を、11個の数式を使用して求めました。

（右図参照）

その方法を応用して、今回は「配列」という

方法を使い、1つの数式で「数字の位置」

を求めて、住所一覧を「地名」と「番地」に

分ける方法をご紹介します。

※

前回のメールサービスはこちらをご覧ください。

【バックナンバー208】関数--住所を地名と番地に分けて表示①

番地は半角数字で表示されているとします。

（例）

住所一覧

地名

番地

渋谷区恵比寿西2-2-8

渋谷区恵比寿西

2-2-8

大田区蒲田33-14-x

大田区蒲田

33-14-x

板橋区常盤台12-27-x

板橋区常盤台

12-27-x

■「配列」を使用して、複数の検索値を1つのFIND関数に設定。

前回と同じく、FIND関数とMIN関数を使用して、数字の位置を求めます。

①FIND関数の検索値に「配列」を使う

　「番地」は数字で始まるので、数字（1,2,3,4,5,6,7,8,9,0）を全て検索値とします。

　前回はそのために、数字ごとにFIND関数の式を作りましたが、そうすると関数が多くなってしまいます。

　そこで今回ご紹介する新しい方法です。

　FIND関数の検索値として {　} （大かっこ）の中に、1～0 の数字をカンマ区切りで設定します。

数式：

=FIND({1,2,3,4,5,6,7,8,9,0},B5)

　※「B5」は、住所「渋谷区恵比寿西2-2-8」

　が入っているセルです。

結果：

#VALUE!

←

しかし、結果はエラー値となってしまいます。

この場合のエラーは、検索値の初めの「1」が、検索対象の中にみつからない場合に出ます。

　　このように、複数の値やセル範囲をカンマ区切りにして {　} （大かっこ）の中に入れ、

　　1つの式の中で取り扱えるようにする方法を「配列」と言います。

②検索対象（住所）に "1234567890" の「ダミー数字」を「&」でつなげる

　エラー値を出さないために、検索値と同じ1～0の数字（ダミー数字）を、検索対象につなげます。

　すると、検索対象は→「渋谷区恵比寿西2-2-81234567890」となり、

　検索値が全て見つかります。

数式：

=FIND({1,2,3,4,5,6,7,8,9,0},B5&"1234567890")

結果：

←

エラー値を出さないように設定できました。

しかし、この結果は、検索値「1」の位置になります。

③MIN関数で囲み、正しい結果を抽出

　　FIND関数だけの式の場合、それぞれの検索結果を表示できず、

　　最初の検索値「1」の位置の値だけが抽出されてしまいます。

←求めたい数字の位置の値は「8」です。

結果：×

　そのため、MIN関数でFIND関数を囲み、FIND関数の結果の最小値を抽出します。

数式：

=MIN(FI

結果：○

数字の値を求める数式が完成しました。

■値を元に、LEFT/SUBSTITUTE関数を使って、地名と番地を表示

地名をLEFT関数、番地をSUBSTITUTE関数　を使用して求めます。

LEFT関数：　

番地より左の文字（地名）を出すために使用します。

SUBSTITUTE関数：

住所から、LEFT関数を利用して出した「地名」の部分を消して、

番地を出すために使用します。

数式は以下のように設定します。

地名を求める数式：

数式：

=LEFT(B5,F5-1)

※1

または

数式：

=LEFT(B5,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B5&"12345678

※2

結果：

渋谷区恵比寿西

・地名を求める式について

※1の式は、別のセルにFIND関数の結果を出して、その値を参照した方法です。

※2の式は、別のセルを使わずに、直接FIND関数を入れた方法です。

番地を求める数式：

数式：

=SUBSTITUTE(B5,G5,"")

※3

結果：

2-2-8

※「サンプル」シートに上記の関数を設定してあります。ご参照下さい。

　（※1、※2、※3 の各番号がついています）

住所一覧を地名と番地に分ける事ができました。

■【補足】番地に全角数字が混じっている場合

番地に半角数字と全角数字が両方ある場合は、以下の例のように、番地を正しく分ける事ができません。

このような場合は、検索対象（住所）を、ASC関数で囲み、住所内の数字を全て「半角数字」として、

数字の位置を出します。

※ASC関数とは、全角の英数カナ文字を、半角文字に変換する関数です

　・ASC関数を使用しない場合

結果

地名

番地

渋谷区恵

渋谷区恵比寿西２-

2-8

　・ASC関数を使用した場合

結果

地名

番地

渋谷区恵

渋谷区恵比寿西

２-2-8

○

→（C148) は住所のセルです。

全角数字が混じった住所でも、地名と番地を分けることができました。

注：

番地は、元の住所と同じ、半角数字と全角数字が混じった状態になります。

数字を半角に統一させたい場合は、番地を求める数式を「ASC関数」で囲みます。

※「サンプル」シートに番地の表示を半角にする数式を設定してあります。ご参照下さい。