[[20030407215436]]　『１セル内に入力された住所を３セルに分割する方法』（ひゆ）　ページの最後に飛ぶ

『１セル内に入力された住所を３セルに分割する方法』（ひゆ）

 １セル内に入力済みの住所を３セルに分割する方法を

知っている方がいらっしゃったらアドバイスお願い致します。

【例】このように１セル内に住所が都道府県名から番地まで入力してあるのを・・・

住所

鹿児島県薩摩郡上甑村平良1-4-8<r>

香川県高松市茜町500

広島県広島市東区曙5-2-20

神奈川県川崎市宮前区五所塚1-3-6

兵庫県城崎郡香住町相谷3-2

京都府京田辺市飯岡3-2-18

京都府城陽市市辺3200-1

埼玉県新座市あたご6-7-18

埼玉県東松山市石橋3-20

北海道室蘭市石川町3-5-20

北海道札幌市豊平区旭町1-17-35

東京都杉並区阿佐谷南3-5-2

東京都世田谷区成城2-1-15

以下のように３セルに一括で分割したいのです。

なんせ住所データ量が膨大なもので(^_^;)

鹿児島県　薩摩郡　　上甑村平良1-4-8

香川県　　高松市　　茜町500

広島県　　広島市　　東区曙5-2-20

神奈川県　川崎市　　宮前区五所塚1-3-6

兵庫県　　城崎郡　　香住町相谷3-2

京都府　　京田辺市　飯岡3-2-18

京都府　　城陽市　　市辺3200-1

埼玉県　　新座市　　あたご6-7-18

埼玉県　　東松山市　石橋3-20

北海道　　室蘭市　　石川町3-5-20

北海道　　札幌市　　豊平区旭町1-17-35

東京都　　杉並区　　阿佐谷南3-5-2

東京都　　世田谷区　成城2-1-15

１セル目を都道府県名

２セル目を市区郡名

３セル目をそれ以降の住所　　と分割したいのです。

今迄は、LEFT(A1,3) MID(A1,4,3) MID(A1,7,30) 等と、

簡単な関数で分割してきたのですが、プログラム作成などの知識が無い為、処理するデータ量が多いので、

４文字の県名(神奈川県など)や１文字の市名(呉市など)

一箇所でも、文字数がずれると、その後ろもずれるという具合で、

簡単な関数のみでは対応できず、なかなか手間取っております(^_^;)

都道府県名で１セル、市区郡名で１セル　等と条件付けして

一気に分割する事ができないものかと悩んでおります。

よいアドバイスありましたら、ご指導下さいませ。

何卒、宜しくお願い致しますm(_ _)m

 都道府県名は下記の式で抽出できますが

A2の住所からB2に都道府県を抽出する式 =IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))

C2に区、市，郡を抽出する式

 =MID(A2,LEN(B2)+1,IF(AND(ISERROR(FIND("郡",A2,1)),ISERROR(FIND("区",A2,1))),FIND("市",A2,1),IF(AND(ISERROR(FIND("市",A2,1)),ISERROR(FIND("区",A2,1))),FIND("郡",A2,1),FIND("区",A2,1)))-LEN(B2))

式が長いので、市(区，郡）の文字列位置の検索式を「市」の範囲名にします。

挿入メニューの[名前][定義]で名前を「市」参照範囲に

 =IF(AND(ISERROR(FIND("郡",$A2,1)),ISERROR(FIND("区",$A2,1))),FIND("市",A2,1),IF(AND(ISERROR(FIND("市",$A2,1)),ISERROR(FIND("区",$A2,1))),FIND("郡",$A2,1),FIND("区",$A2,1)))

と入力します。

C2の式は =MID(A2,LEN(B2)+1,市-LEN(B2))　短くなります。

D2の式は　=RIGHT(A2,LEN(A2)-市)

これで、質問の住所例では希望どおり分割できます。

ただし、特例として

市の抽出は以下7市が市の文字が2文字あります。

千葉県八日市場市、市川市、市原市　栃木県今市市　三重県四日市市　滋賀県八日市市　広島県廿日市市

過去ログで指摘されて苦肉の策で式を作りましたが、汎用性を保つのは大変です。

[単語検索]で「市」とすれば以下の例がありますが、参考になるでしょうか？

[1212] 文字列操作関数を使っての抜き取り投稿者：ゆめちゃん投稿日：00/12/04(Mon) 00:30

http://www.excel.studio-kazu.jp/z_old_log/07.html#u1212

 (シニア)

シニアさん、有難うございます。

早速、試してみたのですが、

D2の式は　=RIGHT(A2,LEN(A2)-市)

の式をコピー＆ペーストでやってみたのですが、#NAME? と

エラーになってましいます。

以下のような住所が

北海道札幌市南区真駒内南町4-2-5

北海道札幌市手稲区前田五条

北海道札幌市手稲区前田五条9-8-27

北海道札幌市手稲区前田八条

秋田県秋田市浜田出小屋19-1

秋田県湯沢市倉内三ツ田35-23

秋田県横手市赤坂荒沼48-9

秋田県仙北郡角館町雲然田中141

秋田県仙北郡中仙町豊川小滝川南45

下記のように各セルに抽出される所までは成功しました。

北海道札幌市南区

北海道札幌市手稲区

秋田県秋田市

秋田県湯沢市

秋田県横手市

秋田県仙北郡

市・区・郡　より続いている文字列をD2の式は　=RIGHT(A2,LEN(A2)-市)

で、取り出そうと試みましたが駄目でした。

なぜでしょうか？

申し訳ありませんが、もう一度ご指導頂けると有難いです。

「市」が２つ付く市名は検索して手直しする事にします。

色々、お手数かけますが宜しくお願い致します。

 B2で都道府県、C2で区市郡が分離できたのであれば

 D2の式を =RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2))　または

 =MID(A2,LEN(B2)+LEN(C2)+1,30)　とされたら如何ですか？

 (シニア)

シニアさん、無事出来ました！有難うございました。

感謝、感謝です、有難うございましたm(_ _)m　(ひゆ)

コメント返信：

[ 一覧(最新更新順) ]

[[20030407215436]] 『１セル内に入力された住所を３セルに分割する方法』（ひゆ） ページの最後に飛ぶ

[[20030407215436]]　『１セル内に入力された住所を３セルに分割する方法』（ひゆ）　ページの最後に飛ぶ