表1有全国所有省、市、区、县、对应的名称,表2内A1单元格填入带“**省”、带“**市”、或带“**区”、带**县,或带**省**市**区、**省**市**县之类的内容后,可以自动得出表1内的省市区县名称。总之就是地址内带县,就得出XX县,带区得出XX市XX区,只带XX市,就得出XX市
例如
亲,如下图,公式很长,下拉:
=IFERROR(MID(A2,FIND("市",A2)+1,FIND("县",A2)-FIND("市",A2)),IF(ISERROR(FIND("区",A2)),IF(LEN(A2)-LEN(SUBSTITUTE(A2,"市",""))=1,MID(A2,IFERROR(FIND("省",A2),0)+1,FIND("市",A2)-IFERROR(FIND("省",A2),0)),MID(A2,IFERROR(FIND("市",A2),0)+1,FIND("☆",SUBSTITUTE(A2,"市","☆",LEN(A2)-LEN(SUBSTITUTE(A2,"市",""))))-IFERROR(FIND("市",A2),0))),MID(A2,IFERROR(FIND("省",A2),0)+1,FIND("市",A2)-IFERROR(FIND("省",A2),0))&"-"&MID(A2,FIND("市",A2)+1,FIND("区",A2)-FIND("市",A2))))
只有一个情况无法解决。你的数据里有的写“富阳”,没有带上“市”。这个无法识别出来。建议你先规范数据。方法为:
按Ctrl+H,上面一行查找填“富阳市”,下面一行替换填“☆”。点击全部替换。
再按Ctrl+H,上面一行查找填“富阳”,下面一行替换填“富阳市”。点击全部替换。
再按Ctrl+H,上面一行查找填“☆”,下面一行替换填“富阳市”。点击全部替换。
嗯,这个公式大概能满足了。有两点小问题,一个是需要规范数据,这个没问题。还有一个就是比如会出现某某开发区之类的地址时,公式会套到开发区上。
那,您的意思是“开发区”就忽略,直接显示“XX市”?
OK,公式修订如下:
=IFERROR(MID(A2,FIND("市",A2)+1,FIND("县",A2)-FIND("市",A2)),IF(OR(ISERROR(FIND("区",A2)),NOT(ISERROR(FIND("开发区",A2)))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,"市",""))=1,MID(A2,IFERROR(FIND("省",A2),0)+1,FIND("市",A2)-IFERROR(FIND("省",A2),0)),MID(A2,IFERROR(FIND("市",A2),0)+1,FIND("☆",SUBSTITUTE(A2,"市","☆",LEN(A2)-LEN(SUBSTITUTE(A2,"市",""))))-IFERROR(FIND("市",A2),0))),MID(A2,IFERROR(FIND("省",A2),0)+1,FIND("市",A2)-IFERROR(FIND("省",A2),0))&"-"&MID(A2,FIND("市",A2)+1,FIND("区",A2)-FIND("市",A2))))
在表2中进行如下操作: (注意所有的替换只在工作表中进行)
1、CTRL+H
查找内容输入:*省
替换为不输入
点击全部替换
2、CTRL+H
查找内容输入:区*
替换为输入:区
点击全部替换
3、CTRL+H
查找内容输入:县*
替换为输入:县
点击全部替换
4、在B1输入:=if(len(a1)<7,SUBSTITUTE(a1,"市”,"市-"),left(a1,3))
双击B1右下角小黑十字(自动填充)。
B列应该是你想要的东西了追问
你好,这个公式用后的效果
虽然不是很准确,还是谢谢你的回答
你问题解决了就好,祝工作愉快。
=IF(COUNTIF(Sheet1!$A$1:$A$100,Sheet2!A1)=1,VLOOKUP(Sheet2!A1,Sheet1!$A$1:$A$100,1,),"")
在Sheet3的A列得到的结果为Sheet2中的A列值与Sheet1中的A列值相同的部分。
=IF(COUNTIF(Sheet1!$A$1:$A$100,Sheet2!A1)=0,VLOOKUP(Sheet1!A1,Sheet1!$A$1:$A$100,1,),"")
下拉填充。
这个公式显示的是在Sheet1!$A$1:$A$100区域找不到Sheet2!A1时,显示Sheet1!A1的值。实际显示的是与Sheet2!A列不同的Sheet1!A列的值。