Ctrl+E并不能搞定全國(guó)所有地址拆分。這里向大家推薦即便市州盟同時(shí)存在也可以完美拆分全國(guó)所有地址的公式。港澳臺(tái)除外。

如果地址中各級(jí)行政單位類別都是統(tǒng)一的，則用CTRL+E提取很方便。但是如下方的地址，是無法用CTRL+E完成提取的。

對(duì)于行政單位類別不同的地址拆分，我們用兩個(gè)公式來完成。公式適合港澳臺(tái)之外的全國(guó)所有地址。

第一個(gè)公式：拆分省、自治區(qū)

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

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

公式解析：

直轄市的第3個(gè)字符都是“市”。用MID(A2,3,1)="市"判斷是否為直轄市，是則為空；不是則用LEFT(A2,MIN(FIND({"省","區(qū)"},A2&"省區(qū)")))提取省、自治區(qū)的名稱。A2&"省區(qū)"的目的是避免查找錯(cuò)誤。

第二個(gè)公式：拆分市、州、盟、區(qū)、縣、鎮(zhèn)等

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

=LET(替換,SUBSTITUTE($A2,TEXTJOIN("",1,$B2:B2),),LEFT(替換,MIN(FIND({"區(qū)","市","州","盟","縣","旗","道","鎮(zhèn)","鄉(xiāng)","木"},替換&"區(qū)市州盟縣旗道鎮(zhèn)鄉(xiāng)木"))))

公式解析：

我國(guó)二、三、四級(jí)行政區(qū)域類目末尾字符就是"市""州""盟""區(qū)""縣""旗""道""鎮(zhèn)""鄉(xiāng)""木"。木，指蘇木，內(nèi)蒙古的一種鄉(xiāng)。

公式將前方已提取的各部分用TEXTJOIN結(jié)合起來，最后替換（SUBSTITUTE）為空；然后再用LEFT分別提取。

說明：

當(dāng)前提取方案是將直轄市從市級(jí)開始填充的，如果需要在省級(jí)別中也填入直轄市名，則需要修改公式。有需要的伙伴可以留言領(lǐng)取。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

讓公式簡(jiǎn)化優(yōu)美的寄生函數(shù)，LET

Excel中的最強(qiáng)助攻——FIND函數(shù)

替換函數(shù)SUBSTITUTE的5個(gè)應(yīng)用技巧

動(dòng)態(tài)的吊牌圖表

版權(quán)申明：

本文作者Mutou；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。