Excel公式技巧：從字符串中提取數字——數字位于字符串開頭

Excel公式技巧：從字符串中提取數字——數字位于字符串開頭

本文主要研究從字符串開頭提取數字的技術：

1. 這些數字是連續的

2. 這些連續的數字位于字符串的開頭

3. 想要的結果是將這些連續的數字返回到單個單元格

對于下面研究的每種解決方案，我們需要在兩種不同的情況下測試其健全性：

1. 字符串中除開頭外其他地方沒有數字的情況，例如123ABC。

2. 字符串中除開頭外其他地方也有數字，要么在末尾，要么在中間，例如123ABC456或123ABC456DEF。

無論字符串中除開頭外是否還有其他數字，將要研究的某些解決方案都可以很好地工作，但有些解決方案則存在局限性。在分析每種解決方案時，將會明確說明。

LOOKUP與LEFT

公式1：

=-LOOKUP(1,-LEFT(A1,ROW(INDIRECT(“1:”& LEN(A1)))))

如果單元格A1中的內容為“123ABC”，那么上述公式1返回“123”。其解析過程如下：

ROW(INDIRECT(“1:” & LEN(A1)))

生成一個由1至單元格A1中字符串長度數的整數組成的單列數組：

{1;2;3;4;5;6}

這樣，公式1變為：

=-LOOKUP(1,-LEFT(A1,{1;2;3;4;5;6}))

由于LOOKUP強制生成數組，因此LEFT在這里不是返回單個值，而是返回由六個值組成的數組，每個值對應于將LEFT的num_chars參數指定為1、2、3、4、5、6應用于A1中的字符串，即：

=-LOOKUP(1,-{“1″;”12″;”123″;”123A”;”123AB”;”123ABC”})

其中的數組乘以-1，得到：

=-LOOKUP(1,{-1;-12;-123;#VALUE!; #VALUE!; #VALUE!})

對于LOOKUP來說，如果在lookup_vector中未找到lookup_value，并且假設lookup_vector中沒有大于lookup_value的值，則該函數將從lookup_vector中返回最后一個值（本例中為數字）。該函數還會忽略lookup_vector中的任何錯誤值。這就是在開始給lookup_vector（通過創建一個由負數、零（如果期望提取的字符串以0開頭例如0123ABC）或錯誤值組成的數組）中的值添加負號的原因，可以確保lookup_value為1永遠是一個充分而合法的選擇。在這里，由于在lookup_vector中找不到1，公式返回數組中最后一個數值，即-123。

當然，這絕對不是處理這種公式結構的唯一方法，只要確保選擇的lookup_value的值足夠大。其實這并不困難，讓lookup_value使用所謂的“大數”（即9.99999999999999E+307，這是Excel中允許的最大正數），確保這種公式構造有效。或者，有些人喜歡僅取“非常大”的值，例如10^10（其好處是看起來不像“大數”那么笨拙）。

由于公式1中LOOKUP函數返回-123，因此在前面添加一個負號使其變為想要的123。

但是，公式1并不可靠。因為某些字符串可能會返回其他結果，例如單元格中的值為12JUN，那么：

=-LOOKUP(1,-LEFT(A1,ROW(INDIRECT(“1:”& LEN(A1)))))

轉換為：

=-LOOKUP(1,-LEFT(A1,{1;2;3;4;5}))

轉換為：

=-LOOKUP(0,-{“1″;”12″;”12J”;”12JU”;”12JUN”})

此時，會轉換為：

=-LOOKUP(0,{-1;-12;#VALUE!;#VALUE!;-43994})

這是由于在強制將“12JUN”轉換成數字時，Excel認為其是日期“2020-6-12”，因此將其轉換為相應的序列數字。此時，LOOKUP函數返回-43994。

當然，這不是唯一會出現這種情況的字符串，實際上，任何可以被Excel解釋為日期的字母數字都會如此，例如30SEP、01FEB等，這也會導致不正確的結果。

此外，公式1對于諸如123E3等形式的字符串也無效，其結果將是123000。因為在通常情況下，將123E3輸入單元格后，Excel會自動將其轉換成科學計數格式。

LEFT與COUNT

公式2：

=0+LEFT(A1,COUNT(0+MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))

這是一個數組公式。

仍以單元格A1中的數據是“123ABC”為例。公式2可以轉換為：

=0+LEFT(A1,COUNT(0+MID(A1,{1;2;3;4;5;6},1)))

轉換為：

=0+LEFT(A1,COUNT(0+{“1″;”2″;”3″;”A”;”B”;”C”}))

轉換為：

=0+LEFT(A1,COUNT({1;2;3;#VALUE!;#VALUE!;#VALUE!}))

COUNT函數忽略錯誤值，得到：

=0+LEFT(A1,3)

結果為：

123

下面，嘗試一個公式2可不可以處理除字符串開頭外其他位置還存在數字的情形，例如如果單元格A1中的數據是“123ABC45”，那么公式2可以轉換為：

=0+LEFT(A1,COUNT(0+MID(A1,{1;2;3;4;5;6;7;8},1)))

轉換為：

=0+LEFT(A1,COUNT(0+{“1″;”2″;”3″;”A”;”B”;”C”;”4″;”5″}))

轉換為：

=0+LEFT(A1,COUNT({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}))

轉換為：

=0+LEFT(A1,5)

轉換為：

=0+”123AB”

結果為：

#VALUE!

原因是字符串的末尾有其他數字，因此COUNT函數統計的數字個數大于字符串開頭的數字個數，這樣LEFT取值仍是字母數字混合的字符串。

LEFT、MATCH與ISNUMBER

公式3：

=0+LEFT(A1,MATCH(FALSE,ISNUMBER(0+MID(A1,ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)

這是一個數組公式。

仍以單元格A1中的數據為“123ABC”，公式3可轉換為：

=0+LEFT(A1,MATCH(FALSE,ISNUMBER({1;2;3;#VALUE!;#VALUE!;#VALUE!}),0)-1)

對于ISNUMBER函數來說，傳遞給它的如果是錯誤值則返回FALSE，因此上述公式可轉換為：

=0+LEFT(A1,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

轉換為：

=0+LEFT(A1,4-1)

轉換為：

=0+LEFT(A1,3)

結果為：

123

在公式3中，MATCH/ISNUMBER組合確保字符串中除開頭以外的數字不會影響最終的結果。例如如果單元格A1中的數據是“123ABC45”，那么公式3可以轉換為：

=0+LEFT(A1,MATCH(FALSE,ISNUMBER({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}),0)-1)

轉換為：

=0+LEFT(A1,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE},0)-1)

轉換為：

=0+LEFT(A1,4-1)

轉換為：

=0+LEFT(A1,3)

結果為：

123

LEFT、MATCH與ISERR

與公式3的構造一致，唯一的不同是使用ISERR函數代替了ISNUMBER函數，并強制返回由數字組成的數組。

公式4：

=0+LEFT(A1,MATCH(1,0+ISERR(0+MID(A1,ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)

這是一個數組公式。

仍以單元格A1中的數據為“123ABC”，公式4可轉換為：

=0+LEFT(A1,MATCH(1,0+ISERR({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}),0)-1)

轉換為：

=0+LEFT(A1,MATCH(1,0+{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE},0)-1)

轉換為：

=0+LEFT(A1,MATCH(1,{0;0;0;1;1;1;0;0},0)-1)

轉換為：

=0+LEFT(A1,4-1)

轉換為：

=0+LEFT(A1,3)

結果為：

123

與公式3一樣，字符串中除開頭有數字外其它部位存在數字時不影響結果。