住所録を市区町村、番地、建物名の３分割にする方法

住所録を市区町村、番地、建物名の３分割にする方法

いつも大変お世話になっております。

以下のような住所データを
市区町村、番地、建物名の３分割にする方法を
教えていただけませんでしょうか。

（例）
A1セル　○○県○○市○○区1-2-3○○マンション456
　↓
B1セル　○○県○○市○○区
C1セル　1-2-3
D1セル　○○マンション456

いろいろなサイトを調べたのですが、
２分割の方法しか見つけることができず困っています．．．

業務のセキュリティの関係でマクロは使用できないため、
関数だけで対応したいです。
どうぞよろしくお願いいたします。

質問者からの補足コメント

• 

  ご覧いただきありがとうございます。
  他の事例の回答等を参考にしながら試したところ、作業用セルを使用することで３分割できるようになりました。
  
  （「住所録からマンション名以下を別セルへ」
  　https://oshiete.goo.ne.jp/qa/3796966.html
  　deepinpactさんの質問に対するmaron--5さんの回答を参考にさせていただきました。
  　ありがとうございます！）
  
  【Ａ１セル】
  ○○県○○市○○区○村1-2-3○○マンション456
  （続く）

    補足日時：2017/06/02 20:33

• 

  【Ｂ１セル（作業用）】
  =IFERROR(LEFT(ASC(A1),LOOKUP(1,0/FIND("-",ASC(A1),COLUMN(1:1)),COLUMN(1:1))+SUMPRODUCT(--ISNUMBER(0/MID(ASC(A1),LOOKUP(1,0/FIND("-",ASC(A1),COLUMN(1:1)),COLUMN(1:1)),{1,2,3,4})))),ASC(A1))
  
  結果→○○県○○市○○区○村1-2-3
  
  【Ｃ１セル】
  =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)
  
  結果→○○県○○市○○区○村
  
  【Ｄ１セル】
  =TRIM(REPLACE(B1,1,LEN(C1),))
  
  結果→1-2-3
  （続く）

    補足日時：2017/06/02 20:34

• 

  【Ｅ１セル】
  =TRIM(REPLACE(A1,1,LEN(B1),))
  
  結果→○○マンション456
  
  そこで新たにご質問です。
  Ｂ１セル（作業用）を使用せずにＡ１セルからＣ１，Ｄ１，Ｅ１セルの結果を得るには、どのような式に変更すればよいでしょうか？
  
  ○○県○○市○○区○村、1-2-3○マンション456　の２分割まではできたのですが、
  1-2-3○マンション456を、1-2-3○、マンション456に分けることができず、
  作業用セルを経由して３分割に至った次第です。
  あと一歩な感じです．．．どうぞよろしくお願いいたします。

    補足日時：2017/06/02 20:35

• d-q-t-pさま、ご質問ありがとうございます。
  ○○県○○市○○区○村、という風に、番地の前に入れたいです。
  補足を投稿させていただきましたのでご確認いただけると幸いです。
  よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2017/06/02 20:40

No.6 回答者： ryo_ky 回答日時： 2017/06/06 12:46

補足を読みまして，とりあえず最初の質問である下記の内容を満たす関数について回答します．

入力
A1：○○県○○市○○区1-2-3○○マンション456

出力
B1：○○県○○市○○区
B2：1-2-3
B3：○○マンション456

関数
B1：=LEFT(A1,FIND("区",A1,1))

B2：=MID(A1,FIND("区",A1,1)+1,LEN(IFERROR(LEFT(ASC(A1),LOOKUP(1,0/FIND("-",ASC(A1),COLUMN(1:1)),COLUMN(1:1))+SUMPRODUCT(--ISNUMBER(0/MID(ASC(A1),LOOKUP(1,0/FIND("-",ASC(A1),COLUMN(1:1)),COLUMN(1:1)),{1,2,3,4})))),ASC(A1)))-FIND("区",A1,1))

B3：=RIGHT(A1,LEN(A1)-LEN(IFERROR(LEFT(ASC(A1),LOOKUP(1,0/FIND("-",ASC(A1),COLUMN(1:1)),COLUMN(1:1))+SUMPRODUCT(--ISNUMBER(0/MID(ASC(A1),LOOKUP(1,0/FIND("-",ASC(A1),COLUMN(1:1)),COLUMN(1:1)),{1,2,3,4})))),ASC(A1))))


これはあくまで"質問文の住所については区切れる"という関数ですので，すべてに適用できるかというのは別問題です．
これについては回答No5さんが指摘しておりますが，区切りのルールが明文化されると良い様に思います．

例えば住所を入力するフォームだと
①都道府県
②市区町村
③市区郡
といった項目に分かれますよね？
そういうルール内容が明らかだと回答が得られるかもしれません．

No.5 回答者： d-q-t-p 回答日時： 2017/06/05 13:13

もっというなら「街区」や「条」など 番地を「-」で区切れない

場所はどのように表現しているのでしょう。他にも京都の住所などはど
うするのですか？　「上ル」とか「下ル」とか「入ル」とか。

1. 数字と記号は半角。
2. [1]以外は全て全角で入力
3. 地名の数字は全て漢数字で入力
4. 番地の区切りは全て「-」

として

=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)

=IFERROR(MID(A1,LEN(C1)+1,LEN(A1)-LEN(C1)-LEN(E1)),"")

=IFERROR(REPLACE(SUBSTITUTE(REPLACE(A1,1,LEN(C1),""),"-",""),1,LEN(-LOOKUP(,-LEFT(SUBSTITUTE(REPLACE(A1,1,LEN(C1),""),"-",""),{1,2,3,4,5,6,7,8,9}))),""),"")

この手の数式は 全ての条件を満たさないと正常に機能しません。

まだ他にもいくつか条件に穴がありますが 説明されていないことは
この際 考えないことにします。

作業列をなくすことを考える前に ご自分で条件を整理して正しく計算
できるか よくよく検証されるべきかと思います。「大体合っていれば
いい」で済むのなら結構ですが。

No.4 回答者： d-q-t-p 回答日時： 2017/06/05 10:31

> maron--5さんの回答を参考に

その式 ちゃんと検証しましたか？

住所分割に ASC関数を使うと確実に失敗します。濁点・半濁点が入っている
住所でおかしくなるからです。

作業列をなくすことを考える前に 正しく計算することを考えるのが先だと思
います。そもそも数式自体 理解できているのでしょうか。

No.3 回答者： sorun2 回答日時： 2017/06/03 11:11

回答としては失格承知で私の感想です。

住所録の人数が数万人もあれば困難かもしれませんが、
私なら、分割パターンを特定できませんので
マクロはできなし、分割パターンも見付からないので、コツコツと手作業で分割したいと思います。
1.住所録をバックアップ
2.作業列に１行目からNoを付番する
3.住所順に並べかえる。
4.分割する位置に【文字】を手入力する、
　その【文字】は住所録に存在しない文字、例えば　\,$,%など
5.データ→区切位置→その【文字】を指定して分割
6.No順に並べかえて完了

2.と3.は省略できるが、並べ替えると分割位置を見つけやすいです。

以上、幼稚な手法で１００人程度のデータを分割した経験です。

No.2 回答者： d-q-t-p 回答日時： 2017/06/02 12:50

地名はどこに入れるのですか？

この回答へのお礼

d-q-t-pさま、ご質問ありがとうございます。
お礼を補足に入力してしまいました。ゴメンなさい。
○○県○○市○○区○村、という風に、番地の前に入れたいです。
他にも補足を投稿させていただきましたのでご確認いただけると幸いです。
よろしくお願いいたします。

お礼日時：2017/06/02 20:44

No.1 回答者： ryo_ky 回答日時： 2017/06/02 12:44

検証はできていませんが，LEFT，RIGHT，LEN，MID，FIND関数を使えばできると思います．

ただ住所の場合，区切るための市や郡をどの様に分けるかが問題になりますね．

この回答へのお礼

ryo_kyさま、ご回答ありがとうございます。
おっしゃるとおり、いろんな関数が必要な状況です。
補足を投稿させていただきましたのでご確認いただけると幸いです。
よろしくお願いいたします。

お礼日時：2017/06/02 20:42