編按：

哈嘍，大家好，今天來(lái)給大家分享一個(gè)字符拆分的問(wèn)題，說(shuō)起字符拆分，大家的第一反應(yīng)可能是Ctrl+E。今天來(lái)給大家講解Ctrl+E解決不了的特殊情況，趕緊來(lái)看一看吧！

這個(gè)案例來(lái)自我們的VIP學(xué)員，這個(gè)是她工作中的遇到的問(wèn)題。

表格是這樣的：

A列是姓名加地址，有下面幾點(diǎn)特殊情況需要注意：

（1）姓名中間有空格，空格的個(gè)數(shù)不定；

（2）姓名和地址之間有空格，空格個(gè)數(shù)也不同

這個(gè)題有一定的普遍意義，覺(jué)得大家的工作中很有可能會(huì)碰到，所以把思路在這里給大家分享一下。

首先，她考慮的是用快速填充——Ctrl+E，試了半天依然不對(duì)，下面我們來(lái)說(shuō)函數(shù)的方法。

分析一下：

從文本中提取字符的函數(shù)主要有LEFT(從左側(cè)提取，提取多少位)，RIGHT(從右側(cè)提取，多位)，MID(從中間提取，提取起點(diǎn)，提取多少位)。本道題的難點(diǎn)在于，不管哪種提取方法，問(wèn)題是不知道提取多少位。

如果先用find去確定空格的位置，在這道題中，有很多連續(xù)的空格，所以，不好用find定位。

所以，我們用的另外一個(gè)，稍微大膽的方法。用substitute替換，把任何一個(gè)空格都替換為1000個(gè)空格。

對(duì)于A2單元格，輸入公式=SUBSTITUTE(A2," ",REPT(" ",1000))

這樣A2就變成了這樣的：

哦級(jí)宏 ................至少1000個(gè)空格............. 北京市三區(qū)

變成這樣以后，提取地址是不是很方便了呢？我就可以用下面的公式

=RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500)

因?yàn)榈刂非懊嬷辽儆?000個(gè)空格，我們從右邊開(kāi)始提取500個(gè)，不會(huì)提取到姓名，但是肯定會(huì)包含所有的地址。

得到的結(jié)果就是：

............空格........... 北京市三區(qū)

然后我們?cè)僭谕饷嫣咨弦粋€(gè)Trim處理一些前面的這些空格，就得到我們想要的地址了。

完整公式為：

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500))

得到的結(jié)果為：北京市三區(qū)

提取姓名：

得到地址以后還沒(méi)有完，我們還需要獲取姓名。有了地址獲取姓名就比較簡(jiǎn)單了。我們先用一個(gè)Substitute函數(shù)把地址替換為空：

=SUBSTITUTE(A2,上一步獲取的地址,"")

得到的結(jié)果為：哦級(jí)宏 ...空格...

然后再用Trim函數(shù)再次去除文本兩端的空格，公式如下：

=TRIM(SUBSTITUTE(A2,上一步獲取的地址,""))

得到的結(jié)果為：哦級(jí)宏

結(jié)果呈現(xiàn)：

先寫(xiě)C2單元格的公式：=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500))

再寫(xiě)B(tài)2單元格的公式：=TRIM(SUBSTITUTE(A2,C2,""))

這里，我們需要先提取地址，然后再提取姓名，剛才已經(jīng)分析了原因。

VBA方案

如果熟悉VBA的小伙伴也可以用代碼來(lái)解決這個(gè)問(wèn)題，代碼有解釋，詳細(xì)如下：

Sub test()
Dim reg As Object, mh, i&, ar()
[b:c].Clear
Set reg = CreateObject("vbscript.regexp") '創(chuàng)建正則對(duì)象reg
reg.Pattern = "(S+ *S+(?= )) *(S+$)"
'設(shè)置reg的匹配樣式，S +表示非空字符，+表示1個(gè)以上， *表示任意個(gè)空格，再跟一串漢字，直到遇上空格。再跟任意個(gè)空格，加上一串漢字結(jié)尾（$表示結(jié)尾）
'將第一串和最后一串分組，用于提取出匹配的字段
ReDim Preserve ar(1 To [a1].End(xlDown).Row, 1 To 2) '重定義數(shù)組大小
For i = 1 To [a1].End(xlDown).Row '循環(huán)區(qū)域中的單元格
 Set mh = reg.Execute(Cells(i, 1).Value) '使用execute方法
 ar(i, 1) = mh(0).SubMatches(0) '賦值數(shù)組，因?yàn)槭峭耆ヅ?，所以只有一個(gè)匹配值，mh的item只有一個(gè)，所以用mh(0).
 ar(i, 2) = mh(0).SubMatches(1) '由于有兩個(gè)括號(hào)的分組，所以在match對(duì)象下的submatches屬性中會(huì)有兩個(gè)結(jié)果的集合，抽取第一個(gè)值用submatches(0) 第二個(gè)值用submatches(1)
Next i
[b1].Resize([a1].End(xlDown).Row, 2) = ar '重定義單元格大小，返回?cái)?shù)組結(jié)果至單元格

以上，便是今天的所有內(nèi)容，大家趕緊來(lái)試一試吧！

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

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

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

相關(guān)推薦：

如何提取品牌信息？LOOKUP函數(shù)有絕招！

沒(méi)有Textjoin函數(shù)，如何解決提取數(shù)據(jù)的問(wèn)題？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

工資表轉(zhuǎn)工資條，VLOOKUP有絕招！

版權(quán)申明：

本文作者柳之；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。