[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『住所の分割』（lion）
お世話になります。
以下は『Excel2007の技 VBA編』からのものですが，これを生かしながら，追加したいことがございます。ご教授ください。
以下はA列にまとめられた住所を，「都道府県」名を切り取りB列へ，それ以外をC列へ貼り付けるものです。
例えば，「東京都府中市」であれば思うとおりにできるのですが，「府中市」から始まるデータであったり，（実際あるかはわかりませんが）「道府町市」というデータがC列にそのまま貼り付けされるにはどこをどうすればよろしいでしょうか。
Sub test()
Dim myAd As String Dim n As Long Dim i As Long Dim ws1 As Worksheet
Set ws1 = Worksheets("Sheet1")
For i = 2 To 8
myAd = ws1.Cells(i, 1).Value
If myAd Like "*[都道府県]*" Then
If Mid(myAd, 4, 1) = " 県 " Then
n = 4
Else
n = 3
End If
Else
n = 0
End If
ws1.Cells(i, 2).Value = Left(myAd, n)
ws1.Cells(i, 3).Value = Mid(myAd, n + 1)
Next i
End Sub
＜ 使用 Excel：Excel2016mac、使用 OS：MacOSX ＞
最近の質問では、埼玉県限定（こういうきわどい住所がない）で、数式でバラす案（半平太さん）がありました。[[20171114141558]]
参考までに、正規表現で頑張ってみた方のページなぞ。
https://qiita.com/zakuroishikuro/items/066421bce820e3c73ce9
（???） 2017/12/15(金) 11:05
＞n = 0 にしていて ＞ws1.Cells(i, 3).Value = Mid(myAd, n + 1) C列に張り付けだから ＞「府中市」から始まるデータであったり これは 処理されてると思うけど普通にC列に張り付くと思うけど
＞「道府町市」というデータ
IFで逃げればいいだけだと思うけど
違うのかな
やってないから
何ともだけど
（Q::） 2017/12/15(金) 11:20
ご教示いただきありがとうございます。私もずいぶんと悩んだのですが，やはりお詳しいお二人がおっしゃるとおり，難しいですよね。範囲を狭めることで可能かもしれませんが，全国データですので，ほかに方法がないか考えてみたいと思います。
ありがとうございます。
（lion） 2017/12/15(金) 11:44
Sub test()
Dim myAd As String
Dim i As Long
Dim n As Long
Dim nMax As Long
With Sheets("Sheet1")
For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
myAd = .Cells(i, 1).Value
nMax = Len(myAd)
For n = 1 To nMax
If Mid(myAd, n, 1) Like "[都道府県]" Then
.Cells(i, 2).Value = Left(myAd, n)
.Cells(i, 3).Value = Mid(myAd, n + 1)
Exit For
End If
Next n
If nMax < n Then
.Cells(i, 3).Value = myAd
End If
Next i
End With
End Sub
（???） 2017/12/15(金) 11:48
Q::さん、「府中市」には「府」があるので "府中市" Like "*[都道府県]*" はTrueになる。 （ねむねむ） 2017/12/15(金) 11:52
ありがとうございます
（Q::） 2017/12/15(金) 12:01
総務省あたりが行政指導して、全国の "都道府県市町村区群" を含む地名を、さいたま市のようにひらがなに変えてくれれば、情報処理が簡単になると思うのですけどねぇ。
Sub test()
Dim myAd As String
Dim myAd2 As String
Dim i As Long
Dim n As Long
Dim nMax As Long
With Sheets("Sheet1")
For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
myAd = .Cells(i, 1).Value
myAd2 = Replace(myAd, "市川市", "いちかわ市")
myAd2 = Replace(myAd2, "府中市", "ふちゅう市")
nMax = Len(myAd2)
For n = 1 To nMax
If Mid(myAd2, n, 1) Like "[都道府県]" Then
.Cells(i, 2).Value = Left(myAd, n)
.Cells(i, 3).Value = Mid(myAd, n + 1)
Exit For
End If
Next n
If nMax < n Then
.Cells(i, 3).Value = myAd
End If
Next i
End With
End Sub
（???） 2017/12/15(金) 12:55
こちらは関数の解説ですが、
ロジックは参考になるのでは？
https://sites.google.com/site/s04728sh/
https://allabout.co.jp/gm/gc/297828/
（まっつわん） 2017/12/15(金) 13:36
都道府県とそれ以外を分離するだけの話でしょ？（都道府県がなければ、C列だけでいい）
47個の都道府県名が、対象文字列の左端にあるかどうかのチェックで済みますよ。
（半平太） 2017/12/15(金) 15:43
ぜひコードを教えていただけないでしょうか。お願いいたします。
（lion） 2017/12/15(金) 16:06
例えば、E列に47都道府県名リストがあるとすれば
Sub test()
Dim ws1 As Worksheet
Dim lastRw As Long
Set ws1 = Worksheets("Sheet1")
With ws1
lastRw = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B2").Resize(lastRw - 1)
.FormulaLocal = "=IFERROR(LOOKUP(1,0/COUNTIF(A2,$E$2:$E$48&""*""),$E$2:$E$48),"""")"
.Offset(, 1).FormulaLocal = "=REPLACE(A2,1,LEN(B2),"""")"
.Resize(, 2).Value = .Resize(, 2).Value
End With
End With
End Sub
<Sheet1 結果図> 行 ____________A____________ _____B_____ _________C_________ _D_ ____ E ____ 1 住所 都道府県名 市区町村名 都道府県名 2 広島県府中市府中町 広島県 府中市府中町 北海道 3 東京都府中市府中町 東京都 府中市府中町 青森県 4 府中市府中町 府中市府中町 岩手県 5 広島県三次市 広島県 三次市 宮城県 6 広島県府中市阿字町 広島県 府中市阿字町 秋田県 7 埼玉県川越市野田町１丁目 埼玉県 川越市野田町１丁目 山形県 8 毛呂山町大字市場 毛呂山町大字市場 福島県 9 川越市野田町１丁目 川越市野田町１丁目 茨城県 10 埼玉県毛呂山町大字市場 埼玉県 毛呂山町大字市場 栃木県 11 庄原市西城町中野 庄原市西城町中野 群馬県 12 川越市野田町１丁目 川越市野田町１丁目 埼玉県 13 府中市荒谷町 府中市荒谷町 千葉県 14 東京都 15 神奈川県 16 新潟県 17 富山県 18 石川県 19 福井県 20 山梨県 21 長野県 22 岐阜県 23 静岡県 24 愛知県 25 三重県 26 滋賀県 27 京都府 28 大阪府 29 兵庫県 30 奈良県 31 和歌山県 32 鳥取県 33 島根県 34 岡山県 35 広島県 36 山口県 37 徳島県 38 香川県 39 愛媛県 40 高知県 41 福岡県 42 佐賀県 43 長崎県 44 熊本県 45 大分県 46 宮崎県 47 鹿児島県 48 沖縄県
数式で簡単にできるってことですけどね・・
都道府県名リストが無く、且つ数式だけでもそんなに難度は高くないと思いますが、 都道府県名リストなんて誰でも簡単に作れるんですから、 余計な神経使わなきゃならない数式を作る気は起きないです。
（半平太） 2017/12/15(金) 17:39
現状では… ＾＾；
A列に住所があるとして
B列に
=IF(OR(LEFT($A1,4)="和歌山県",LEFT($A1,4)="鹿児島県",LEFT($A1,4)="神奈川県"),LEFT($A1,4),LEFT($A1,3))
C列に
=IF(OR(LEFT($A1,4)="和歌山県",LEFT($A1,4)="鹿児島県",LEFT($A1,4)="神奈川県"),MID($A1,5,LEN($A1)-4),MID($A1,4,LEN($A1)-3))
Ａ列の最終情報行までフィルコピー
でも
（隠居じーさん） 2017/12/15(金) 18:53
＞でも
でも？
ここ読みましたか？ ↓ ＞例えば，「東京都府中市」であれば思うとおりにできるのですが，「府中市」から始まるデータであったり，
（半平太） 2017/12/15(金) 20:09
むむむ ＾＾；
ご指摘の通りですね。
左から３文字目、または四文字めが、”都”、”道”、”府”、”県”以外で、かつ郡市町村を含む？？？
（？？）～？？？ （ＴＴ）
弱った老人の頭脳の適用外のよ～です。わたくしの案は廃案と言う事で。
失礼いたしました。
m(__)m
（隠居じーさん） 2017/12/16(土) 07:21
"=IFERROR(LOOKUP(1,0/COUNTIF(A2,$E$2:$E$48&""*""),$E$2:$E$48),"""")"
ゼロで割らなければ、全て鹿児島県に
割れば、見事に、各、都道府県が。
なぜゼロで割れば…成功なのか
皆様は、お解りなのでしょうね…私は不思議です、でも完璧作動 ＾＾ｖ
お教えいただければ幸甚です。
ネット調査しましたが。
範囲が文字列で
条件が範囲の中の任意の文字列
の使用例は有りませんでした。
只々、感嘆いたしております。
とても、勉強になります。
別スレがよければ立てます。
宜しくお願い致します。
m(_ _)m
（隠居じーさん） 2017/12/17(日) 09:53
LOOKUP関数の仕様が分かってないと理解できないです。
ヘルプの解説では 昇順に並んでいる配列に対して、検索値と同値(それがなければ内輪の最大値）をヒットする。 (※その際、エラー値は無視される)
これを逆手にとって、以下の性質を利用する 昇順であろうが無かろうが、検索値が配列の最大値より少しでも大きければ、最後尾の値をヒットする。 何故なら、昇順と思い込んでいるので、同値が見当たらなければ、最後尾が内輪の最大値と判定される
※「ゼロで割らなければ、全て鹿児島県に」とは言えないです。 途中に1(=該当県）があったりするので、検索値(=1) は最大値より大きいと言えなくなります。
あとは自力で考えてください。
（半平太） 2017/12/17(日) 11:08
何かと、ご多忙の中、ご説明戴き、
有難うございます。
ご指摘の関数、もうすこし掘り下げて調べてみます。
老人のボケ防止に最適な宿題です。
工夫次第で様々な使い方ができるのですね～！
頑張ってみます。
<< _ _ >>
（隠居じーさん） 2017/12/17(日) 11:56
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.