從文本中截取的套路

06-04

從文本中截取內容通常都會用到Left、Right、Mid、Find這幾個函數。這幾個函數本身並不難，參數也比較少，很容易理解。但是用他們組合起來，或者加上其他函數組合起來截取指定的內容，那就不一定簡單了。今天我們從基礎開始到略深入，講一下截取的幾個套路。

基礎

Left的英文意思是左邊，作為Excel函數就代表從左邊開始截取字元。

Right的英文意思是右邊，作為Excel函數就代表從右邊開始截取字元。

Mid的英文意思是中間，作為Excel函數就代表從中間開始截取字元，當然也可以從第一個字元開始截取。

Find的英文意思是查找，作為Excel函數就代表查找一個字元串在另一個字元串中的位置，並且可以指定從第幾個字元開始查找，注意這裡指的是從第幾個字元開始搜索，返回的仍然是它本身所在的位置。

這幾個函數的語法都比較簡單，從字面意思也很容易判斷如何使用。

語法

LEFT(text, [num_chars])

RIGHT(text,[num_chars])

MID(text, start_num, num_chars)

FIND(find_text, within_text, [start_num])

這幾個函數都有一個按位元組處理的函數就是在函數結尾加上B（B代表Byte）,在計算機中，一個漢字、全形符號等代表兩個位元組，所以大家需要注意區分字元個數、位元組個數。

這些函數跟上面的函數具有相似的語法，如下：

LEFTB(text, [num_bytes])

RIGHTB(text,[num_bytes])

MIDB(text, start_num, num_bytes)

FINDB(find_text, within_text, [start_num])

大家注意看Left、Right、Find、LeftB、RightB、FindB函數的最後一個參數都是可選的，如果不填的話就默認為1。

簡單示例

通過以下幾個示例，我們可以先簡單了解一下這幾個函數。

這幾個函數看起來簡單，但是組合起來也可以很複雜，運用得當的話也能解決很多實際問題。

經常見到前面加負號或兩個負號？

這是因為Left、Right、Mid函數返回的結果是文本，如果你要截取數字並得到真正的數值，就得用兩個負號將它變成真正的數值。當然了，我們也可以用其他方式來轉換，比如乘以1。

1應用實例：提取數字

情景一：數字在左邊起始位置

思路：

1、從左邊開始截取字元，分別截取1個、2個、3個字元……

2、然後將截取的值轉換成數值查找最大的值。

公式1：

=-LOOKUP(,-LEFT(A2,ROW($1:$99)))

公式2：這是數組公式，需要按Ctrl Shift Enter

=MAX(IFERROR(--LEFT(A3,ROW($1:$100)),0))

解釋：

公式中ROW($1:$99)是為了簡便起見，現實中一般也不會有這麼大的數。Right加上這個數組參數就表示從左邊開始分別截取1~99個字元。

Left前面的負號表示將Left截取的結果轉換成負數，如果是文本，那麼就轉換成錯誤值。

Lookup第一個參數省略了，其實第一個參數就是個0，第二個參數除錯誤值外都是負數，這樣就找到了最後一個負數，也就代表了長度最大的一個有效數值。

最後Lookup外面再用一個負號就將轉換成正值了。

第2個公式中的IfError是為了屏蔽將文本轉換成數值造成的錯誤值。

關於Lookup的用法，請參考以下文章。

弱水三千，只取一瓢 - Lookup經典用法三例

情景二：數字在右邊結束位置

公式跟上面的比較相似，只不過是改成用Right從右邊開始截取。

公式1：

=-LOOKUP(,-RIGHT(A9,ROW($1:$99)))

公式2：這是數組公式。

=MAX(IFERROR(--RIGHT(A10,ROW($1:$100)),0))

情景三：數字在不確定的位置

這時就需要用到Mid了，因為Mid可以從指定的位置開始截取字元。

思路：

1、先找到第一個數字出現的位置，然後從這個位置開始截取字元，截取1個、2個、3個……

2、將截取的內容轉換成數值並求最大的那個數。

公式1：數組公式

=-LOOKUP(,-MID(A15,MATCH(,0/MID(A15,ROW($1:$99),1),),ROW($1:$99)))

說明：

ROW($1:$99)是簡略的寫法，如果字元數超過99個，則可以將99改為更大的數，或者用ROW(INDIRECT("1:"&LEN(A15)))來代替（見公式2）。

0/MID(A15,ROW($1:$99),1)，如果是數字，則結果是0，否則結果是錯誤值。

MATCH(,0/MID(A15,ROW($1:$99),1),)查找第一個數字出現的位置。

MID(A15,MATCH(,0/MID(A15,ROW($1:$99),1),),ROW($1:$99))從第一個數字的位置開始截取，分別截取1~99個字元，然後用負號轉換成負值，如果是文本則變成錯誤值。

最後用Lookup查找最後一個負數數值，最外面用一個負號轉換成正數。

公式2：數組公式

=-LOOKUP(,-MID(A16,MATCH(,0/MID(A16,ROW(INDIRECT("1:"&LEN(A16))),1),),ROW($1:$99)))

公式3：數組公式

=MAX(IFERROR(--MID(A17,MATCH(,0/MID(A17,ROW($1:$99),1),),ROW($1:$99)),0))

說明：這個Max的公式跟上面的情境中使用原理一致。

公式4：數組公式

=-LOOKUP(,-MID(A17,MIN(FIND(ROW($1:$10)-1,A17&1/17)),ROW($1:$99)))

說明：這裡用Find查找數字出現的位置，注意其中1/17這種用法，相信很多同學都不明白，使用1/17是因為1/17的結果中包含0~9所有數字，是為了Find的容錯處理，可以簡化公式，當然可以採用直接寫數字這種方法"1234567890"（見公式5），也可以用IfError來容錯（見公式6）。

公式5：數組公式

=-LOOKUP(,-MID(A18,MIN(FIND(ROW($1:$10)-1,A18&"1234567890")),ROW($1:$99)))

公式6：數組公式

=-LOOKUP(,-MID(A19,MIN(IFERROR(FIND(ROW($1:$10)-1,A19),9E 307)),ROW($1:$99)))

總之各種公式都是用相應的套路，學會之後靈活組合就可以了。

情景四：數字有前導0存在

在情景3的基礎上又增加了一點，數字中有前導0存在，需要提取全部的數字。

公式：數組公式

=MID(A26,MIN(FIND(ROW($1:$10)-1,A26&1/17)),MAX(IF(ISNUMBER(--MID(A26,ROW(INDIRECT("1:"&LEN(A26))),1)),ROW(INDIRECT("1:"&LEN(A26))),0))-MIN(IFERROR(FIND(ROW($1:$10)-1,A26),E9 307)) 1)

說明：

這裡有前導0存在就不能使用處理數值那樣的方法了。

求最後一個數字出現的位置的公式MAX(IF(ISNUMBER(--MID(A26,ROW(INDIRECT("1:"&LEN(A26))),1)),ROW(INDIRECT("1:"&LEN(A26))),0))，看起來很複雜，其實原理就是每次截取一個字元，負負號轉換後看看是不是數字，如果是的話就記錄位置，否則的話就記錄0，看最大的位置是哪個。

情景五：金額填充到每個單元格

如下圖所示，將金額填充到後面的每個單元格中。

在C1中輸入以下公式，然後向右拉，向下拉。

=LEFT(RIGHT(REPT(" ",11)&"￥"&$A2*100,12-COLUMN(A:A)))

解釋說明：

從「億」到「分」總共11個單元格，先把數字乘以100是為了獲取小數位，並且可以去掉包含小數點的麻煩。

REPT(" ",11)是為了填充空格，當數字位數不夠時可以截取到空格。

12-COLUMN(A:A)，當在C列時，表示截取11個字元，當在M列時表示截取1個字元。

先用Right截取相應長度的字元，再用Left截取最左邊的字元。

2應用實例：提取身份證中的信息

身份證中1~6位數表示地區信息，7~14位表示出生年月日（YYYYMMDD格式），15~17位表示同一地區的同年同月同日出生的人的順序號，奇數表示男，偶數表示女。最後一位數字是校驗碼。

所以我們可以在C2單元格輸入以下公式來獲取出生日期。

=TEXT(--MID(B2,7,8),"0-00-00")

注意：

這個結果是文本格式，如果需要轉換成真正的日期，只需要在Text前面加上兩個負號即可，如下。

=--TEXT(--MID(B2,7,8),"0-00-00")

在D2單元格輸入以下公式。

=IF(MOD(MID(B2,15,3),2),"男","女")

說明：

Mod是求餘數的函數，MOD(MID(B2,15,3),2)結果是1表示是奇數，代表男性，結果是0時表示是偶數，代表女性。

在公式中數字0代表false，除此之外的其他數字都代表True。

小夥伴們，這些套路你都學會了嗎？

暫時沒搞明白也沒關係，收藏起來，說不定哪天就用到了呢！

--End--