Ctrl+E并不能搞定全国所有地址拆分。这里向大家推荐即便市州盟同时存在也可以完美拆分全国所有地址的公式。港澳台除外。

如果地址中各级行政单位类别都是统一的，则用CTRL+E提取很方便。但是如下方的地址，是无法用CTRL+E完成提取的。

对于行政单位类别不同的地址拆分，我们用两个公式来完成。公式适合港澳台之外的全国所有地址。

第一个公式：拆分省、自治区

在B2中输入公式并下拉填充：

=IF(MID(A2,3,1)="市","",LEFT(A2,MIN(FIND({"省","区"},A2&"省区"))))

公式解析：

直辖市的第3个字符都是“市”。用MID(A2,3,1)="市"判断是否为直辖市，是则为空；不是则用LEFT(A2,MIN(FIND({"省","区"},A2&"省区")))提取省、自治区的名称。A2&"省区"的目的是避免查找错误。

第二个公式：拆分市、州、盟、区、县、镇等

在C2中输入公式并下拉右拉填充：

=LET(替换,SUBSTITUTE($A2,TEXTJOIN("",1,$B2:B2),),LEFT(替换,MIN(FIND({"区","市","州","盟","县","旗","道","镇","乡","木"},替换&"区市州盟县旗道镇乡木"))))

公式解析：

我国二、三、四级行政区域类目末尾字符就是"市""州""盟""区""县""旗""道""镇""乡""木"。木，指苏木，内蒙古的一种乡。

公式将前方已提取的各部分用TEXTJOIN结合起来，最后替换（SUBSTITUTE）为空；然后再用LEFT分别提取。

说明：

当前提取方案是将直辖市从市级开始填充的，如果需要在省级别中也填入直辖市名，则需要修改公式。有需要的伙伴可以留言领取。

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

让公式简化优美的寄生函数，LET

Excel中的最强助攻——FIND函数

替换函数SUBSTITUTE的5个应用技巧

动态的吊牌图表

版权申明：

本文作者Mutou；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。