利用公式按指定間隔符提取數(shù)據(jù)

王志軍

如圖1所示，A列是很小一部分?jǐn)?shù)據(jù)源，每個單元格的數(shù)據(jù)以符號“/”作為間隔符組合在一起，例如“亞洲/中國/江蘇/蘇州”，現(xiàn)在希望提取其中的部分?jǐn)?shù)據(jù)，雖然可以使用“分列”提取數(shù)據(jù)，但由于這里的數(shù)據(jù)源并不規(guī)范，而且分列之后的整理也是相當(dāng)?shù)穆闊?。其實，我們可以利用公式完成上述提取任?wù)：

一、提取第一個間隔符“/”前的數(shù)據(jù)

選擇B2單元格，在編輯欄輸入公式“=LEFT（A2，F(xiàn)IND（”/”，A2）-1）”，這里的FIND函數(shù)用來發(fā)現(xiàn)“/”在A2單元格中首次出現(xiàn)的位置，接下來使用LEFT函數(shù)從左向右提取該長度的字符，公式執(zhí)行之后向下拖拽或雙擊填充柄，即可得到圖2所示的提取結(jié)果。

二、提取最后一個間隔符“/”后的數(shù)據(jù)

這個要求與前述要求正好相反，選擇C2單元格，在編輯欄輸入公式“=TRIM（RIGHT（SUBSTITUTE（A2，”/”，REPT（” ”，10 0》，10 0》”，“SUBSTITUTE（A2，”/”，REPT（“”，100》”這部分公式是將A2中的“/”替換為100個空格，接下來RIGHT函數(shù)從右邊提取100個字符，這100個字符必然包括了最后一個“/”后的數(shù)據(jù)以及大部分的空格，最后使用TRIM函數(shù)清除多余的空格，圖3即我們所需要的結(jié)果。

這里當(dāng)然也可以使用“100”之外的其他大數(shù)據(jù)，主要作用是拉大數(shù)據(jù)之間的距離。

三、提取指定位數(shù)間隔符之間的數(shù)據(jù)

例如提取第2個“/”和第3個“/”之間的數(shù)據(jù)，選擇D2單元格，在編輯欄輸入公式“=TRIM（MID（SUBSTITUTE（A2，”/”，REPT（””，100》，100，100》”，這個公式列之后的后續(xù)處理，在B2單元格輸入以下公式“=TRIM（MID（SUBSTITUTE（$A2，”/”，REPT（””，100》，COLUMN（A1）*100-99，100》”，這里使用“COLUMN（A1）*100-99”取動態(tài)區(qū)間，隨著公式的向右拖拽，依次提取第1-101-201個字符起的100個字符結(jié)果，最后依然使用TRIM函數(shù)清理空格。公式執(zhí)行之后向右、向下拖拽填充柄，很快就可以得到圖5所示的分列效果。