スキルアップ・問題解決なら、モーグにおまかせ！

A列が住所で、B列には都道府県、C列には市区町村、D列にはそれ以降に分割する方法を知りたいです。
 
以前、郵便局のホームページからダウンロードしたデータを使って分割したような記憶もありますが、その方法も思い出せません。
 
どのような方法でも良いのですが、Excelで住所を分割したいです。
よろしくお願いします。

対応できないものもありますが...
 
　　　　　　　　　　　　　　　 A　　　　 B　　　　　　　　　　 C　　　 D
 1　　　　　　　　　　　　　住所　都道府県　　　　　　　市区町村　　残り
 2　　　　　　　　東京都港区ああ　　東京都　　　　　　　　　港区　　ああ
 3　　　　　北海道苫小牧市いいい　　北海道　　　　　　　苫小牧市　いいい
 4　　鹿児島県いちき串木野市いい　鹿児島県　　　　いちき串木野市　　いい
 5　　　　　千葉県四街道市かかか　　千葉県　　　　　　　四街道市　かかか
 6　　　　　　　千葉県市川市きき　　千葉県　　　　　　　　市川市　　きき
 7　　　　　　　福岡県太宰府市く　　福岡県　　　　　　　太宰府市　　　く
 8　　　宮崎県北諸県郡三股町けけ　　宮崎県　　　　北諸県郡三股町　　けけ
 9　　　茨城県那珂郡東海村こここ　　茨城県　　　　　那珂郡東海村　こここ
10　山梨県南都留郡富士河口湖町さ　　山梨県　南都留郡富士河口湖町　　　さ
11　　　　　　　　四街道市かかか　　　　　　　　　　　　四街道市　かかか
12　　　　　　　　太宰府市ききき　　　　　　　　　　　　太宰府市　ききき
 
B2
=IF(OR(MID(A2,2,2)={"街道","宰府","諸県"}),"",LEFT(A2,IF(OR(MID(A2,3,1)={"都","道","府","県"}),3,IF(MID(A2,4,1)="県",4,))))
下方向・↓
C2
=LET(s,SEQUENCE(9,,LEN(B2)+2),REPLACE(LEFT(A2,MAX(s*(MID(A2,s,1)={"市","区","町","村"}))),1,LEN(B2),))
下方向・↓
D2
=REPLACE(A2,1,SUM(LEN(B2:C2)),)
下方向・↓

んなっとさん
 
ありがとうございます。
思っていたよりも難しい式でした。自分ではとても考えつかなかったです。
 
"街道","宰府","諸県"については住所に入っていなければ不要でしょうか。
これから職場に行って試してみます。

ダウンロードデータと照合する方法。市区町村の抽出の不具合が少し減ります。
  
●Sheet2にダウンロードデータがあり、そのH列に市区町村がある場合...
  
　　　 A　　　B　　　　C　D　E　F　　　 G　　　　　　 H　　　　　　　　　　　 I
1　01101　060　　0600000　　　　　 北海道　札幌市中央区　　以下に掲載がない場合
2　01101　064　　0640941　　　　　 北海道　札幌市中央区　　　　　　　　　旭ケ丘
3　01101　060　　0600041　　　　　 北海道　札幌市中央区　　　　　　　　　大通東
4　01101　060　　0600042　　　　　 北海道　札幌市中央区　大通西（１〜１９丁目）
  
●Sheet1
  
　　　　　　　　　　　　A　　　　 B　　　　 C　　　　　 D
1　　　　　　　　　　住所　都道府県　市区町村　　　　残り
2　北海道室蘭市清水町きき　　北海道　　室蘭市　清水町きき
3　千葉県成田市田町くくく　　千葉県　　成田市　田町くくく
  
C2
=IF(A2="","",LET(a,LEFT(REPLACE(A2,1,LEN(B2),),SEQUENCE(9,,2)),
b,FILTER(a,ISNUMBER(FIND(RIGHT(a,1),"市区町村"))),
IFERROR(LOOKUP(1,0/XMATCH(b,Sheet2!H:H),b),"")))
下方向・↓

都道府県とそれ以降に分けるのは、関数だけで可能ですが、
3分割(都道府県、市区町村、それ以降)するのは、完璧にするには例外処理が多すぎて難しいですね。
 
ちょっとWEB検索してみたら下記のようなWEBサーピスを見つけました。
 
住所の分割 ★ | ExcelAPI
https://excelapi.org/docs/post/address-separate/
 
WEBSERVICE関数を使って分割結果を取得できるようです。
 
フリー版だと、1万件/日の制限はありますが、そんなに使用しないなら使えるかも。
 
使用例の画像がありますが、それを見る限りは結構使えそうです。

hatenaさん
 
ありがとうございます。
関数だけだと難しいことはわかりましたが、一旦関数でなんとかなりました。
 
今日は時間なくAPIは確認できませんでした。
明日見てみます。
調べていただきありがとうございます。

んなっとさんhatenaさん
 
一旦わかったので良かったです。
またわからないことが出てきたら伺いたいです。
 
ありがとうございます。