|
- Sub test()
- arr = Sheets("表1").Range("a1").CurrentRegion.Resize(, 6)
- Set regx = CreateObject("vbscript.regexp")
- For i = 2 To UBound(arr)
- If arr(i, 4) <> "" Then
- regx.Pattern = "[^ 一-龥]"
- arr(i, 4) = regx.Replace(arr(i, 4), "")
- ar = Split(Application.Trim(arr(i, 4)), " ")
- If UBound(ar) > 0 Then s = ar(1) Else s = ar(0)
- regx.Pattern = "^([^市县区]+?)省|(北京|上海|天津|重庆)市?|^([^市县区]+?区)"
- Set mh = regx.Execute(s)
- If mh.Count <> 0 Then
- arr(i, 4) = mh(0).submatches(0) & mh(0).submatches(1) & mh(0).submatches(2)
- Else
- arr(i, 4) = ""
- End If
- s = regx.Replace(s, "")
- regx.Pattern = "^(.县)|(.+?)[市县区]"
- Set mh = regx.Execute(s)
- If mh.Count <> 0 Then arr(i, 5) = mh(0).submatches(0) & mh(0).submatches(1)
- s = regx.Replace(s, "")
- regx.Pattern = "^(.县|.镇)|^(.+?)县|^([^区]+?)[镇乡]|^(.+?区)"
- Set mh = regx.Execute(s)
- If mh.Count <> 0 Then
- If mh(0).submatches(0) <> "" Then
- arr(i, 6) = mh(0).submatches(0)
- ElseIf mh(0).submatches(1) <> "" Then
- arr(i, 6) = mh(0).submatches(1)
- Else
- arr(i, 6) = IIf(mh(0).submatches(2) <> "", mh(0).submatches(2), mh(0).submatches(3))
- End If
- End If
- End If
- Next
- Sheets("表2").Range("a1").Resize(UBound(arr), 6) = arr
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|