一个表格里有很多地址，我想分别提取出 “市” 和 “区/县” ——”市“（只有一个”市“，就提取该”市“的内容。有两个”市“，则填写第二个”市“的内容） ——”区/县“（有“区”填写”区“的内容。没有”区“，就填写县的内容）的相应内容 一： ... 展开

我希望是这样子提取的，分开每一个单元格来提取出，省，城市（取第二个市），区县（有县写县，无县写区，无区就不写）。****表示后面还有具体的地址


如果只提取“市”或者同级别的“州”，就是：

=IF(C6="","",SUBSTITUTE(SUBSTITUTE(IF(COUNT(FIND("省",C6),FIND("市",C6))=2,MID(C6,FIND("省",C6)+1,FIND("市",C6)-1-FIND("省",C6)),IF(COUNT(FIND("省",C6),FIND("州",C6))=2,MID(C6,FIND("省",C6)+1,FIND("州",C6)-1-FIND("省",C6)),C6)),"省",),"市",))

如果更详细就是：

=CHOOSE(1+(1*LEN(TRIM(C6))-LEN(SUBSTITUTE(TRIM(C6)," ",""))),MID(MID(C6,1,MAX(IF(ISERROR(FIND("区",C6)),0,FIND("区",C6)),IF(ISERROR(FIND("市",C6)),0,FIND("市",C6)))),LARGE(CHOOSE(ROW(1:4),IF(ISERROR(FIND("区",C6)),0,FIND("区",C6)),IF(ISERROR(FIND("省",C6)),0,FIND("省",C6)),IF(ISERROR(FIND("市",C6)),0,FIND("市",C6)),IF(ISERROR(FIND("治州",C6)),0,1+FIND("治州",C6))),2)+1,100),MID(MID(C6,1,MAX(IF(ISERROR(FIND("区",C6)),0,FIND("区",C6)),IF(ISERROR(FIND("市",C6)),0,FIND("市",C6)))),LARGE(CHOOSE(ROW(1:4),IF(ISERROR(FIND("区",C6)),0,FIND("区",C6)),IF(ISERROR(FIND("省",C6)),0,FIND("省",C6)),IF(ISERROR(FIND("市",C6)),0,FIND("市",C6)),IF(ISERROR(FIND("治州",C6)),0,1+FIND("治州",C6))),2)+1,100),MID(SUBSTITUTE(MID(TRIM(C6),1,FIND("^",SUBSTITUTE(TRIM(C6)," ","^",2)))," ","^",1),FIND("^",SUBSTITUTE(MID(TRIM(C6),1,FIND("^",SUBSTITUTE(TRIM(C6)," ","^",2)))," ","^",1))+1,100),MID(SUBSTITUTE(MID(TRIM(C6),1,FIND("^",SUBSTITUTE(TRIM(C6)," ","^",3)))," ","^",2),FIND("^",SUBSTITUTE(MID(TRIM(C6),1,FIND("^",SUBSTITUTE(TRIM(C6)," ","^",3)))," ","^",2))+1,100))


如果还要更复杂的EXCEL判断公式，请联系我。仔细描述需求。然后我在评估报价。