|
发表于 2015-4-8 10:09
|
显示全部楼层
本楼为最佳答案
3楼基础上稍微改了一下,加了自治区、直辖市的判断,并最多可4级显示- Sub test()
- Dim str As String
- Dim arr
- arr = Sheet1.Range("a1:g" & Sheet1.[A65536].End(3).Row)
- For x = 2 To UBound(arr)
- str = arr(x, 4): arr(x, 4) = ""
- If str <> "" Then
- str = Split(str, " ")(1)
- If InStr(str, "省") Then arr(x, 4) = Split(str, "省")(0) & "省": str = Split(str, "省")(1)
- If InStr(str, "自治区") Then arr(x, 4) = Split(str, "自治区")(0) & "自治区": str = Split(str, "自治区")(1)
- If InStr("上海重庆北京天津", Left(str, 2)) Then arr(x, 4) = Left(str, 3): str = Mid(str, 4)
-
- If InStr(str, "市") Then arr(x, 5) = Split(str, "市")(0) & "市": str = Split(str, "市")(1)
- c = IIf(arr(x, 5) <> "", 6, 5)
- If InStr(str, "县") Then arr(x, c) = Split(str, "县")(0) & "县": str = Split(str, "县")(1)
- If InStr(str, "区") Then arr(x, c) = Split(str, "区")(0) & "区": str = Split(str, "区")(1)
- c = IIf(arr(x, 6) <> "", 7, 6)
- If InStr(str, "镇") Then arr(x, c) = Split(str, "镇")(0) & "镇"
- End If
- Next x
- Sheet2.Range("a1").Resize(UBound(arr), 7) = arr
- End Sub
复制代码 |
评分
-
查看全部评分
|