Excel如何提取单元格内倒数第二个空格分隔的子字符串

解决方案 #

公式修正逻辑 #

原有公式的问题是定位了最后1个空格的位置，截取的是空格后所有内容，自然只能拿到最后一段的省份缩写。要获取倒数第二段，只需把定位的空格位置调整为倒数第2个空格即可。

全版本兼容公式（适配macOS版所有Excel） #

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))+1,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))-1)

逻辑说明：

• 用LEN(A2)-LEN(SUBSTITUTE(A2," ",""))计算出字符串内的总空格数S
• 把第S-1个空格（也就是倒数第二个空格）替换为特殊字符CHAR(1)，定位该字符的位置，加1就是目标段的起始位置
• 再定位第S个空格（最后一个空格）的位置，两个位置的差值减1就是目标段的长度，刚好截取到需要的comune字段。

简化版公式（仅支持Excel 365/2021及以上版本，含mac版） #

如果你的Excel版本支持新文本函数，可以用更简洁的写法：

=TEXTAFTER(TEXTBEFORE(A2," ",-1)," ",-1)

逻辑说明：先用TEXTBEFORE(A2," ",-1)提取最后一个空格之前的全部内容，再用TEXTAFTER提取这部分内容里最后一个空格之后的内容，就是倒数第二段。

批量处理备选方案 #

因为已经确认所有地址格式固定，也可以直接用「数据-分列」功能，按空格作为分隔符拆分后，直接取第4列的内容即可，适合一次性处理大量数据。

内容的提问来源于stack exchange，提问作者Aneema