1.以数据在A1为例

方法1:

省

=LEFT(A1,MIN(FIND({"省","市","区"}, A1&"省市区")))

市

=SUBSTITUTE((LEFT(A1,MIN(FIND({"市","区","县"},A1&"市区县")))),B1,"")

区

=SUBSTITUTE((LEFT(A1,MIN(FIND({"区","县","镇"},A1&"区县镇")))),B1&C1,"")

或者

=SUBSTITUTE((LEFT(A1,MIN(FIND({"区","县","镇"},A1&"区县镇")))),(LEFT(A1,MIN(FIND({"市","区","县"},A1&"市区县")))),"")

镇

=SUBSTITUTE((LEFT(A1,MIN(FIND("镇",A1&"镇")))),B1&C1&D1,"")

方法2：

省

=LEFT(A1,MIN(FIND({"省","市","区"},A1&"省市区")))

市

=MID(LEFT(A1,FIND("市",A1)),FIND("省",A1)+1,5)

区

=MID(LEFT(A1,FIND("区",A1)),FIND("市",A1)+1,5)

镇

=MID(LEFT(A1,FIND("镇",A1)),FIND("区",A1)+1,5)