截取字符的问题

huipu20220102

标黄色的是输错啦？

6108

我增加了一个辅助列，再来一个超长公式，暂时算是解决了，谢谢各位大佬。
=IF(ISNUMBER(SEARCH("省",A1)),LEFT(A1,(FIND("省",A1)-1)),LEFT(A1,(FIND("市",A1)-1)))&CHAR(10)&IF(LEN(B1)-LEN(SUBSTITUTE(B1,"市",""))>1,MID(B1,FIND("市",B1,1)+1,FIND("市",B1,FIND("市",B1,1)+1)-FIND("市",B1,1)-1),IF(AND(ISNUMBER(FIND({"市","县"},B1))),MID(B1,FIND("市",B1,1)+1,FIND("县",B1,FIND("市",B1,1)+1)-FIND("市",B1,1)-1),IF(ISNUMBER(SEARCH("县",B1)),LEFT(B1,FIND("县",B1)-1),IF(ISERROR(FIND("市",B1)),LEFT(B1,FIND("区",B1)-1),LEFT(B1,FIND("市",B1)-1)))))

6108

=IF(ISNUMBER(SEARCH("省",A1)),LEFT(A1,(FIND("省",A1)-1)),LEFT(A1,(FIND("市",A1)-1)))&CHAR(10)&IF(LEN(B1)-LEN(SUBSTITUTE(B1,"市",""))>1,MID(B1,FIND("市",B1,1)+1,FIND("市",B1,FIND("市",B1,1)+1)-FIND("市",B1,1)-1),IF(AND(ISNUMBER(FIND({"市","县"},B1))),MID(B1,FIND("市",B1,1)+1,FIND("县",B1,FIND("市",B1,1)+1)-FIND("市",B1,1)-1),IF(ISNUMBER(SEARCH("县",B1)),LEFT(B1,FIND("县",B1)-1),IF(ISERROR(FIND("市",B1)),LEFT(B1,FIND("区",B1)-1),LEFT(B1,FIND("市",B1)-1)))))

杨建营

正则提取，参与一下。

6108

Sub test()
Dim i&
Dim arr, reg, mh
Set reg = CreateObject("vbscript.regexp")
arr = [a1].CurrentRegion.Resize(, 2)
With reg
    .Global = True
    .Pattern = "([\u4e00-\u9fa5]{2,4})(?:[省|市|自治区])([\u4e00-\u9fa5]{2,4})(?:[县|市|区])"
    For i = 1 To UBound(arr)
        If arr(i, 1) <> "" Then
            Set mh = .Execute(arr(i, 1))
            arr(i, 2) = mh(0).submatches(0) & mh(0).submatches(1)
        End If
    Next
End With
[a1].Resize(UBound(arr), 2) = arr
End Sub
宏就这么简单？牛啊