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

■文字列内の数字の位置を抽出し、その位置の値で文字列を分けて表示する事ができます。
この方法を使い、今回は住所を地名と番地に分ける方法をご紹介します。

☆対応方法：FIND関数・MIN関数をつかって数字の位置を抽出します。その値からLEFT関数で数字より前の文字を、SUBSTITUTE関数で数字から後ろの文字をそれぞれ分けます。

※ご注意下さい。今回ご紹介する方法は、番地が半角の数字で始まることを条件としています。全角数字をご利用の場合はFIND関数で使用しているダミー数字を変更する必要があります。

住所を地名（番地の前）と番地に分けます。

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

住所の中で数字が始まる位置を出して、その位置から地名と番地に分ける方法をご紹介します。

住所：

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

地名：

渋谷区恵比寿西

番地：

2-2-8

■FIND関数とMIN関数を使い、数字の始まる位置を求める

まず数字の始まる位置を、「FIND関数」・「MIN関数」を利用して求めます。

①FIND関数を使用し、数字（1,2,3,4,5,6,7,8,9,0）を検索値として、検索対象（住所）内の数字位置を抽出します。

②MIN関数を使用して、FIND関数で求めた値の中から最小の値を抽出します。

[表1]

検索値

数式

結果

←（C10）は住所が入ったセルです。

=FIND(1,C10)

#VALUE!

=FIND(2,C10)

=FIND(3,C10)

#VALUE!

=FIND(4,C10)

#VALUE!

=FIND(5,C10)

#VALUE!

=FIND(6,C10)

#VALUE!

=FIND(7,C10)

#VALUE!

=FIND(8,C10)

=FIND(9,C10)

#VALUE!

=FIND(0,C10)

#VALUE!

最小値

=MIN(E26:E35)

#VALUE!

そこで、エラー値が出ないように、FIND関数の検索対象にダミー数字を設定します。

数字「1234567890」を、検索対象セルに「&」でつなげます。

=FIND(1,(C10)&"1234567890"))

「渋谷区恵比寿西2-2-81234567890」

[表2]

検索値

数式

結果

　[表2]のように、ダミー数字の設定により、

=FIND(1,C10&"1234567

　全ての検索値に結果が返されます。

=FIND(1,C10&"1234567

最小値

=MIN(F52:F61)

　→[表2]結果内(F43:F52)の最小値

MIN関数で最小値が抽出されました。

※

「FIND関数」・「FIND関数とSEARCH関数の違い」については、バックナンバーをご参照下さい。

【バックナンバー103】関数--FIND関数

※

「MIN関数」については、バックナンバーをご参照下さい。

【バックナンバー107】関数--AVARAGE/MAX/MIN関数

■LEFT/SUBSTITUTE関数を使い地名と番地に分ける

[表3]のように、数字の位置が抽出されましたら、

①地名をLEFT関数

②番地をSUBSTITUTE関数

を使用して求めます。（以下の数式内の「F77」は、住所のセルです）

[表3]

検索値

結果

住所：

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

地名：

渋谷区恵比寿西

番地：

2-2-8

最小値

①LEFT関数：

=LEFT(F

　または

=LEFT(F

　[表3]で最小値を抽出したセル

[表3]結果の値一覧から、最小値を抽出

・住所の番地より左の文字（地名）を出します。

結果：

渋谷区恵比寿西

注意：　

「"番地が始まる位置の値"-1」が、地名の文字数となりますので、

MIN関数で抽出した値から1を引きます。（青色の下線部分）

1を引かないと、「渋谷区恵比寿西2」と、番地が1文字出てしまいます。

②SUBSTITUTE関数：

=SUBSTITUTE(F7

LEFT関数で出された「地名」のセル

・地名部分を空白にして、番地部分を出します。

結果：

2-2-8

番地が無い住所の場合は、LEFT関数の結果の欄に住所が全て出ます。

SUBSTITUTE関数の結果は空欄になります。

※ 今回の内容を「見本」のシートにまとめてありますので、ご参照下さい。

今回、文字位置の値を求める為に、10個のFIND関数を使用しましたが、今回の方法を応用して、

FIND関数１個にまとめて値を求める方法を、次回のメールサービスでご紹介します。