[[20030407215436]] 『1セル内に入力された住所を3セルに分割する方法』(ひゆ) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『1セル内に入力された住所を3セルに分割する方法』(ひゆ)

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

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

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

住所

鹿児島県薩摩郡上甑村平良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

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

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

鹿児島県 薩摩郡  上甑村平良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

1セル目を都道府県名

2セル目を市区郡名

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

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

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

4文字の県名(神奈川県など)や1文字の市名(呉市など)

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

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

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

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

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

何卒、宜しくお願い致します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)-市)

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

なぜでしょうか?

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

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

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


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

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

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

 (シニア)


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

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


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.