エクセルの関数で１つのセルに入っている住所を丁目までと番地以降に分けたい

エクセルで１つのセルに東京都○○区○○町○丁目○ー○というように住所が入力されており、これを関数で「東京都○○区○○町○丁目」と「○ー○」に分けたいのですがどうすればよいでしょうか？
また東京都××区××町×ー×というように丁目が入らない住所に関しては「東京都××区××町」と「×ー×」に分離したいです。
上記の処理が可能な関数を教えてください。

No.5 回答者： grumpy_the_dwarf 回答日時： 2008/12/02 10:21

住居表示を分解するなら、自治体までと町域以下で分けましょう。

koko88okokさんもおっしゃるとおり、番地のみのセルはトラブルの
元です。毎年この時期、たくさんの人が「番地が日付になった～」
と泣いていますよ。

その自治体までで分ける方法ですが、hallo-2007さんのおっしゃる
とおり、東京都だけでも町田市や武蔵村山市の例もありますように
単純に文字列操作関数で「市区町村」を検索しても成功しません。
一般的には自治体一覧を用意して、match関数で検索します。
たとえば下記URLから東京都の表を見て、Excelにペーストします。
http://www.lasdec.nippon-net.ne.jp/cms/1,0,14.html

団体名以外の列は不要ですから消して、団体名の頭に「東京都」を
追加し、末尾に空白があるので消し、文字コード順に並べ替えて準
備完了です。あ、ついでに自治体一覧って名前をつけましょうか。

A1に「東京都世田谷区世田谷４－２１－２７ 」と入ってたら、
B1: =index(自治体一覧,match(A1,自治体一覧))
C1: =mid(A1,len(B1)+1,len(A1))
これで、B1に「東京都世田谷区」、C1に「世田谷４－２１－２７」
ときれいに分離できました。

どうしても番地だけ分離したい場合も、この応用で郵便番号一覧あ
たりのデータを使えば可能です。日本郵便のサイトにありますね。

No.4 回答者： koko88okok 回答日時： 2008/12/02 00:08

老婆心ながら、その分離はお止めになることをお勧めします。

番地のない住所は郵便番号の列があれば「郵便番号変換ウィザード」の簡単な操作で生成できますし、「1-2-3」などを入力したセルはExcelによって日付に変換されてしまいます。

また、差し込み印刷とか、他の住所録から参照するなどの際トラブルの元になりかねません。

「5.Excel で校正　＞　5-5　Excel 郵便番号変換ウィザード」
http://www.morgan.co.jp/kiso/5excel-5.htm
「郵便番号変換ウィザード」
http://hamachan.info/excel/yuhenkan.html

No.3 回答者： hallo-2007 回答日時： 2008/12/01 23:07

一応、紹介として

A列に住所
B列に=IF(COUNTIF(A2,"*丁目*"),LEFT(A2,FIND("丁目",A2)+1),LEFT(A2,FIND("町",A2)))
C列に=MID(A21,LEN(B21)+1,100)
とかは思いつきますが、住所の場合、町田市とかもありますし難しいです。

No.2 回答者： sige1701 回答日時： 2008/12/01 22:59

参考までに

B1=LEFT(A1,LOOKUP(100,FIND({"町","丁目"},A1)+{0,1}))
C1=SUBSTITUTE(A1,B1,"")

No.1 回答者： exchangekytgi 回答日時： 2008/12/01 22:33

一発で出来るようなそんな便利な関数は

ないと思いますよ。
ここは場合分け(IF)とmid関数やinstr関数などを
駆使してプログラムを作成するしかないでしょう。
例えば
mid("テスト区てすと町○○丁目１－２",1,instr(1,"テスト区てすと町○○丁目１－２","丁目")+1)
→テスト区てすと町○○丁目
mid("テスト区てすと町○○丁目１－２",instr(1,"テスト区てすと町○○丁目１－２","丁目")+2)
→１－２
等々。