エクセルで、１セルの中の住所を分離させる方法を教えて

エクセルで、１セルの中の住所を分離させる方法を教えて

１セルの中に、都道府県市町村番地部屋番全てが入力されているのを、町村番地部屋番を次のセルに移したいのですが、都道府県市まではほぼ同じで複数行あり多いのですが何とかなりますか？教えて。

No.7 回答者： mt2008 回答日時： 2010/07/27 17:05

ANo.6です。

ごめんなさい、トリミング前の画像を添付しちゃいました。
あれじゃ見えませんよね。

No.6 回答者： mt2008 回答日時： 2010/07/27 17:03

現在、市名や郡名に「市」や「郡」が使われている地域は以下の10箇所です。

・千葉県市川市
・千葉県市原市
・三重県四日市市
・広島県廿日市市
・福島県郡山市
・愛知県蒲郡市
・奈良県大和郡山市
・奈良県高市郡
・岐阜県郡上市
・福岡県小郡市

これ以外の地名は最初に「区」「市」「郡」が出てきた所で区切ればいいです。
添付の図を見てください。10の地域をF1:F10に入れました。

住所データはA列に入っている物とします。
B列は、10の地域に合致するか否かを調べる作業列として使います。
B1に、　=SUMPRODUCT(ISNUMBER(FIND(F$1:F$10,A1))*ROW(F$1:F$10))

C列は、区、市、郡までを表示します。
C1に、　=IF(B1>0,INDEX(F$1:F$10,B1),LEFT(A1,MIN(FIND("市",A1&"市区郡"),FIND("区",A1&"市区郡"),FIND("郡",A1&"市区郡"))))

D列にそれ以降を表示、
D1に、　=RIGHT(A1,LEN(A1)-LEN(C1))

B1:D1を下方向にコピーしてください。

＃市町村合併等で地名が変わるとメンテナンスが必要になります。

No.5 回答者： imogasi 回答日時： 2010/07/26 23:47

関数では完全なものを作れない。

またプログラムを作るにしても、十分なものは、全国の市区町村名などのあるデータベース（郵便番号辞書のようなもの）が必要で質問者には無理でしょう。
ーー
ここは大幅にざっくりしたもので考えると
下記のユーザー定義関数を作って
標準モジュールにコピペ（エクセルシート画面で、ALT+F11を押して出る画面にコピペ）
Function si(a)
s = Array(" ", "区", "市", "町", "村")
For i = 0 To UBound(s)
p = InStr(a, s(i))
If p <> 0 Then
si = p
Exit Function
Else
End If
Next i
End Function
ーーー
シートのA列に住所があるとして、D列に市区町村を出すとして
D1に=LEFT(A1,si(A1))
下方向に式を複写。
それ以下の町アザ丁目番地は、E列のE1なりに
=SUBSTITUTE(A1,D1,"")
大阪市北区の例があるので
s = Array("区", "市", "町", "村")
の並びは区の方を先に持ってくる。
ーー
中身はでたらめな例
大阪市中央区木村大阪市中央区木村
秩父市紙町秩父市紙町
北津軽群河合町新市北津軽群河合町新市
東村山市青葉１－１１東村山市青葉１－１１
青森市津軽町青森市津軽町
北津軽群河合町 新市北津軽群河合町 新市
東村山市八日市１－１１東村山市八日市１－１１
東村山市町屋１－１２東村山市町屋１－１２
３番目のような例は出たら、A列データに人間が１半角スペースを入れてデータを変えてみる（６番目）
ーー
最終人間のチェックは欠かせない。

No.4 回答者： CoalTar 回答日時： 2010/07/26 21:58

郵便番号に変換(郵便番号ウィザードでもOK)

郵便番号からkt電話郵便アドインの関数を使い住所を分けて取り出せます。
あとは取り出した市区町村をもとにFIND関数などで分離可能と思います
http://www.h3.dion.ne.jp/~sakatsu/TelPost.htm

参考URL：http://www.vector.co.jp/soft/win95/personal/se34 …

No.3 回答者： MackyNo1 回答日時： 2010/07/26 21:17

市川市や八日市市などのように、市の名前に「市」が入っているケースにも対応できる（最後の市以降の文字列を抽出する）ようにするなら、以下のような数式になります。

=MID(A1,MAX(INDEX((MID(A1,COLUMN(1:1),1)="市")*COLUMN(1:1),))+1,100)

ただし上記の式の場合、市が入っていない郡などの場合は、そのまま表示されますので、郡の部分でも区切りたいなら上記の式全体をA1セル部分に代入し、市の代わりに郡で区切る数式にすれば、郡以降の町村名を抽出することができます。

逆に町村名に市が入っているような場合（たとえば～市場など）は、その文字以降のデータを抽出してしまうので、いずれにしろ数式だけで完璧に表示できる手段はないので、最終的にチェックする必要があります（たとえば文字列中に市が２つ以上入っているセルのみ抽出する）。

No.2 回答者： mar00 回答日時： 2010/07/26 20:47

=RIGHT(B2,LEN(B2)-FIND("市",B2,1))

ではどうでしょうか。

No.1 回答者： kmetu 回答日時： 2010/07/26 20:42

=LEFT(A1,FIND("市",A1))

で市までの文字列

=RIGHT(A1,LEN(A1)-FIND("市",A1))

で市から右の文字列が取り出せますので、それぞれをコピーし値の貼り付けをしてください。