Excelで１セル内の住所を市郡町街と丁目以下の地番等に分離する方法は？

Sheet１のA列に例えば群馬県内の住所のリストがあります。（架空の住所です。）
前橋市池端町3-2-21……（セルA１）
桐生市黒保根町水沼7-3-5
高崎市大沢町1-18-204
沼田市佐山町8-6-5-402
勢多郡北橘村下箱田396
多野郡吉井町岩井27-5
以下1,000件程度

Sheet２のA1:B1538に郵政公社からダウンロードした群馬県内の郵便番号一覧表があり、A列に市郡町街大字等、B列に対応する郵便番号があります。（名前定義を仮に「〒」とします）

Sheet１の住所の郵便番号を検索する必要がありますが、そのためには住所の丁目地番より左の部分（市郡町街大字等）を分離しなければなりません。
これをマクロを使わずに１個の計算式で表示したいといろいろ工夫しましたが、どうもいい知恵が浮かびません。仕方なく一旦B列（B１)に
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,9,1),8,1),7,1),6,1),5,1),4,1),3,1),2,1)
という長たらしいネスト式を使って「2～9」の数値をすべて「1」に置き換え、
C列（C1)に　=LEFT(A1,FIND(1,B1)-1)
と計算式を入れて市郡町街大字等を抽出し、D列（D１）に
VLOOKUP(C1,〒,2,FALSE)　と入れてようやく郵便番号を検索しております。
結果としてはこれで正しい答は出るのですが、標準搭載の関数だけで（マクロを使わずに）何かもっと簡単な方法がありはしないかという気がしてなりません。
ご存知の方があればどうか教えてください。（OS等はWinMe→Excel2000Proです。）

No.1 ベストアンサー 回答者： BLUEPIXY 回答日時： 2005/08/15 19:06

=LEFT(A1,LENB(A1)-LEN(A1))

でどうでしょうか？
Ａ１で言えば"3-2-21"と削除するべき部分が１バイト文字になっていますので、バイトで文字数を数えた時の長さを文字数で数えた時の長さを引けば、
２バイト文字で構成される文字の長さがでます。

この回答へのお礼

イヤー驚きました。番地が半角文字であることに着目し、バイトで数えた文字数と単なる文字数との差が漢字部分の文字数になるなんて、正にコロンブスの卵。何かありそうだとは思ってましたけど私のボケ頭では思いつきませんでした。
こんな簡単なことだったんですね。仮に番地部分が全角数字＆記号だったとしてもASC関数で半角にすればよいし、眼からウロコが落ちました。ありがとうございました。

お礼日時：2005/08/15 21:38

No.4 回答者： Wendy02 回答日時： 2005/08/15 21:47

こんばんは。

紹介されている数式をみて、私のオリジナルということで出しておきます。

=LEFT(A1,MATCH(FALSE,INDEX(ISERROR(MID(A1,ROW($A$1:$A$256),1)*1),,),0)-1)

これは、住所の後ろのほうに、全角が入っても区切ることが出来ます。正確に言うと、最初に出てきた数字の手前で切り分けます。
配列数式ですが、配列の確定が必要ありません。

この回答へのお礼

イヤーいろんな方法があるもんですね。今のところ数式を解析して理解するまでには至ってませんが、とりあえず試してみて正しい答えが出ることが分かりました。今後ほかの面で使えそうなので参考にさせていただきます。
ありがとうございました。

お礼日時：2005/08/15 23:44

No.3 回答者： noname#25507 回答日時： 2005/08/15 19:12

数字を境目にするなら

http://www.kotaete-net.net/bbs04007.aspx?intDlgM …

細かく分けるなら
http://www.okweb.ne.jp/kotaeru.php3?q=501935&rev=1

この回答へのお礼

ご掲示のＵＲＬを開いたところ、ASCによる全角文字の半角化部分を除き、ANo２、masa_019さんの回答と同じでしたが、説明等もあり、ある程度は理解できました。
ありがとうございました。

お礼日時：2005/08/15 23:32

No.2 回答者： masa_019 回答日時： 2005/08/15 19:10

こんばんは。

住所の文字列の中の最初に出てくる数値より左を取り出します。

A1に住所：前橋市池端町3-2-21があったとして、B1に
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-1)
これで、前橋市池端町が取り出せると思います。

この回答へのお礼

エラー防止のための1234567890の結合、FIND関数による０位置の検索までは分かりましたが、数値の最小値を求めるMIN関数でなぜ数値左端の位置が算出されるのか、その仕組みが今のところボンクラ頭では理解出来ておりません。しかし結果として正しく答えが出ることが分かりましたのでお礼申し上げます。
ただ、数式としてはBLUEPIXYさんの「=LEFT(A1,LENB(A1)-LEN(A1)) 」の方が簡明だという感じがしています。
いずれにしてもありがとうございました。

お礼日時：2005/08/15 23:18