在工作中有很多這樣的應用場景,需要分別提取字符串中的數字和漢字,比如下圖:
上圖中的A列:需要分別提取科目代碼和科目名稱;C列需要提取數字;E列和G列分別提取人名和手機號,如果是安裝了Excel 2013以後的版本,可以使用“快速填充”功能來提取,輕鬆快捷。關於快速填充的介紹請參見:
“快速填充”:讓文本函數和分列功能下崗 - 知乎專欄
如果沒有2013版,那麼使用函數來編制公式就稍有點複雜了,尤其是C列、G列。
那使用基本功能能不能分離提取呢?答案是可以的!
這就是我們今天在前面的《練習題052(答案):將一列數據整理成五列 - 知乎專欄》已經使用過的技巧:
查找+兩端對齊
下面我們先來看一下兩個功能主要用到的基本知識點介紹:
一、查找功能中可使用通配符
在Excel的查找中可以使用通配符,通配符?代表任意單個字符,通配符*代表任意多個字符,比如“龍?凡”可代表“龍逸凡”、“龍平凡”、“龍不凡”,但不能“龍逸非凡”,因為龍和凡之間有二個字。而:“龍*凡”則即可代表“龍逸凡”、“龍平凡”、“龍不凡”,也能代表“龍逸非凡”,還能代表“龍一二三四凡”,因為星號*可代表多個字符。
二、查找結果列表框可排序,還可通過選擇列表框中的值選中相應的單元格
查找功能的查找列表框可以排序,比如在下圖的表中,選中A列,然後在查找欄輸入星號*,然後點擊查找全部,可將A列所有的值列出。點擊查找結果列表框中值,即可按值進行升序排序
排序後的結果如下:
我們可以按住Ctrl鍵或SHIFT鍵,選擇多個結果,比如選擇A後,然後按住SHIFT,再點擊Z,即可將A、B、Y、Z值所在單元格選中。
三、兩端對齊的基本知識
兩端對齊在Excel的老版本中叫內容重排,就是單元格的寬度不夠時,會將單元格的值拆分後依次往下進行重排。
比如下圖中的A1、B1單元格中的值都是“《“偷懶”的技術:打造財務Excel達人》”,
我們將B列寬度設為只有一個字符的寬度,然後點擊“開始”選項卡的填充按鈕旁的下拉箭頭,點擊“兩端對齊”,就會將內容重排,重排後效果如B列所示:
我們將B列寬度設為四個字符的寬度,選中B列,再點擊“兩端對齊”,那麼內容重排後的效果如下:
我們可以利用此特點,只要將單元格寬度設為一個字的寬度,就可將字符串按單個字進行拆分。
需要注意的是,兩端對齊有個特點:“連續的數字、字母”會做為一個整體,並不會拆分。如前面第四張圖,拆分後的B13單元格中的“Excel”並沒有拆分為單個字符,而是做為一個整體。
我們正好可以利用這個特點,結合前面查找結果的排序功能,來分離提取數字和漢字。
四、“查找功能+兩端對齊”雙劍合璧,就可實現“快速填充”的功能!
下面就不寫文字了,直接點擊圖片看GIF動圖
1、提取科目代碼:
2、提取科目名稱
3、提取姓名:
注意,兩端對齊後,使用常規的方法,川普和普京會連在一起,張三丰和布袋和尚會連在一起。要讓其分離,可以使用剪貼板先將C1:C6單元格區域的值粘貼到一個單元格,粘貼到一個單元格後,每一行後會有一換行符。然後再對此單元格進行兩端對齊。選定手機號時,需同時將看起來是空白的D8、D19兩單元格選中(實際上不是空白,有不可見字符),然後清除內容。
4、帶班級情況下的提取姓名
此種情況需使用查找替換先將“班”前的字符全刪除掉,然後參照第3個GIF動畫。