如何在EXCEL表格提取地址中的城市名称

Question

如题所述

Answer 1

以A2为存放要提取的数据源单元格，为例：

1、在B2提取省，用公式：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"省","省@"),"市","市@"),"区","区@"),"@",REPT(" ","99")),COLUMN(A1)*99-98,99))

2、在C2提取市，用公式：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"省","省@"),"市","市@"),"区","区@"),"@",REPT(" ","99")),COLUMN(B1)*99-98,99))

3、在D2提取区，用公式：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"省","省@"),"市","市@"),"区","区@"),"@",REPT(" ","99")),COLUMN(C1)*99-98,99))

4、在E2提取地址，用公式：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"省","省@"),"市","市@"),"区","区@"),"@",REPT(" ","99")),COLUMN(D1)*99-98,99))

5、在F2提取楼，单元，用公式：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"省","省@"),"市","市@"),"区","区@"),"@",REPT(" ","99")),COLUMN(E1)*99-98,99))

Answer 2

在B2单元格输入以下公式，然后向填充公式。=RIGHT(LEFT(C2,FIND("市",C2)-1),LEN(LEFT(C2,FIND("市",C2)-1))-FIND("",LEFT(C2,FIND("市",C2)-1)))表示：从地址栏截取左边到“市”前的字符串，然后从右截取空格后的字符串。

Answer 3

都是大神，不看数据就能回答！
有没有人能告诉我下面这个数据，能得到省市吗？
北京市读者文摘报社驻上海市办事处
无数据无合适的解答