混合內容中提取數據,原理都有了

混合內容中提取數據,原理都有了

我們來看看以下的這樣一個數據。

混合內容中提取數據,原理都有了

有同學問:「怎樣用公式將A列的數據分別提取成B、C列呢?」

在這裡告訴大家一個正確的寫公式的過程:

1、碰到問題,一定要先分析問題,查找規律;

2、之後將問題拆解,使用我們平實的清晰的邏輯描述清楚;

3、使用函數將思路翻譯成Excel語言;

4、調整細節,使公式具有更強的通用性和容錯性。

那麼我們靜下心來仔細看看原始數據,幫助這位同學來做下分析。

<方案一>:

通過仔細觀察,我們發現,這些數據全都是中文與字母數字的區分,而字母數字的部分恰好是7位長度,於是根據文本函數,在C2單元格很容易得到:

=RIGHT(A2,7)

那麼B列的產品名稱呢?就是把型號刪除的部分,那就把字母數字替換掉就行啦,借用C2已經得到的結果,我們在B2寫下這樣的公式:

=SUBSTITUTE(A2,C2,”)

<方案二>:

我們觀察的更加細致一些呢,發現每一個數字都是以0開頭,而中文是在數字0前面兩位之前的部分,那麼根據這個特色,我們就想到了使用FIND函數來查找0的位置。

=FIND(0,A2)

綜合使用,在B2寫公式:

=LEFT(A2,FIND(0,A2)-2)

在C2寫公式:

=MID(A2,FIND(0,A2)-1,99)

這樣的題目,我們使用兩種方法搞定,只需要耐心一些,仔細分析數據特點就好。

但是,還沒完……

如果數據中的型號,並不是固定的7位長度,也沒有固定的字符,而是像下面這樣的數據的話,那該怎麼處理呢?

混合內容中提取數據,原理都有了

經過前面的學習,大家可能已經形成了自己的分析和思維方式,我們來看看這些數據要怎麼處理。

首先補充一個基礎知識:字符與字節的區別

函數LEN始終將每個字符(不管是單字節還是雙字節)按1計數,數LENB會將每個雙字節字符按2計數,否則,函數LENB會將每個字符按1計數。

這個是Excel的幫助信息中所寫的內容,看上去晦澀難懂,我們日常的使用就可以簡單記憶:

每一個英文字母、數字、以及英文狀態下的標點符號,都是1個字節寬度;

每一個中文字符、以及中文標點符號,都是2個字節寬度。

有了這樣的一個基礎概念,我們來看看處理方案:

<方案一>:

首先通過長度的差異來取值:

=LEN(A2)

返回的結果是9,因為A2單元格有9個字符。

=LENB(A2)

返回的結果是12,因為A2單元格的3個漢字每個占2個字節寬度,再加上6個英文、數字每個占1個字節寬度,總計12字節的寬度。

仔細觀察:

LENB(A2)-LEN(A2)的這個差值,恰好等於3,就是A2單元格中漢字的個數。

LEN(A2)-(LENB(A2)-LEN(A2)),通過LEN減去漢字的長度,恰好就等於剩下的字母、數字的長度6。

做好以上鋪墊,於是在B2寫公式:

=LEFT(A2,LENB(A2)-LEN(A2))

在C2寫公式:

=RIGHT(A2,LEN(A2)*2-LENB(A2)))

就完美的分別提取產品和型號到B、C列當中。

<方案二>:

通過觀察,我們可不可以以英文字母作為起點呢?雖然字母不一樣,有A、B、T,但我們希望找到一個簡單的辦法,找到這些第一次出現的位置。

好的,我們來引入一種通配符的概念,那就是英文半角狀態下的問號(?),在可以使用通配符的函數當中它可以指代任意的一個字符。

什麼樣的查找字符函數可以使用通配符呢?

SEARCH呼之欲出。

進一步,如果這裡單單只用SEARCH顯然是無法解決問題的,那麼就得召喚他的兄弟SEARCHB出場。在文本函數中,有n多帶有B的函數,他們的計算都是按照「字節」,而不是「字符」來統計的。

於是有:

= SEARCHB(‘?’,A2)

這個結果返回數字7,通過SEARCHB查找第一個「單字節」的位置,因為每一個漢字都代表2個字節寬度,所以SEARCHB在只查找那些單身字符時,這些成雙配對的字符全部都忽視不見,這樣就找到了A2單元格中的字母B。前面3個漢字,總計6個字節寬度,而B恰好在第7個字節的位置,所以結果是7。

有了這個分割點,於是進一步我們就得到了B2的公式:

=LEFTB(A2,SEARCHB(‘?’,A2)-1)

以及C2的公式:

=MIDB(A2,SEARCHB(‘?’,A2),99)

LEFTB、MIDB都是按照字節來計算的。

思路決定了你的公式是什麼樣子。寫公式,一定是先有思路,再翻譯成公式。

但是,還沒完。。。

如果數據更加複雜難搞呢?那就參加Excel函數實戰100例課程吧。

作者:翟振福 Excel函數實戰100例學習班講師