Excel公式技巧：從字符串中提取數(shù)字——數(shù)字位于字符串開(kāi)頭

Excel公式技巧：從字符串中提取數(shù)字——數(shù)字位于字符串開(kāi)頭

本文主要研究從字符串開(kāi)頭提取數(shù)字的技術(shù)：

1. 這些數(shù)字是連續(xù)的

2. 這些連續(xù)的數(shù)字位于字符串的開(kāi)頭

3. 想要的結(jié)果是將這些連續(xù)的數(shù)字返回到單個(gè)單元格

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

1. 字符串中除開(kāi)頭外其他地方?jīng)]有數(shù)字的情況，例如123ABC。

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

無(wú)論字符串中除開(kāi)頭外是否還有其他數(shù)字，將要研究的某些解決方案都可以很好地工作，但有些解決方案則存在局限性。在分析每種解決方案時(shí)，將會(huì)明確說(shuō)明。

LOOKUP與LEFT

公式1：

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

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

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

生成一個(gè)由1至單元格A1中字符串長(zhǎng)度數(shù)的整數(shù)組成的單列數(shù)組：

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

這樣，公式1變?yōu)椋?/p>

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

由于LOOKUP強(qiáng)制生成數(shù)組，因此LEFT在這里不是返回單個(gè)值，而是返回由六個(gè)值組成的數(shù)組，每個(gè)值對(duì)應(yīng)于將LEFT的num_chars參數(shù)指定為1、2、3、4、5、6應(yīng)用于A1中的字符串，即：

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

其中的數(shù)組乘以-1，得到：

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

對(duì)于LOOKUP來(lái)說(shuō)，如果在lookup_vector中未找到lookup_value，并且假設(shè)lookup_vector中沒(méi)有大于lookup_value的值，則該函數(shù)將從lookup_vector中返回最后一個(gè)值（本例中為數(shù)字）。該函數(shù)還會(huì)忽略lookup_vector中的任何錯(cuò)誤值。這就是在開(kāi)始給lookup_vector（通過(guò)創(chuàng)建一個(gè)由負(fù)數(shù)、零（如果期望提取的字符串以0開(kāi)頭例如0123ABC）或錯(cuò)誤值組成的數(shù)組）中的值添加負(fù)號(hào)的原因，可以確保lookup_value為1永遠(yuǎn)是一個(gè)充分而合法的選擇。在這里，由于在lookup_vector中找不到1，公式返回?cái)?shù)組中最后一個(gè)數(shù)值，即-123。

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

由于公式1中LOOKUP函數(shù)返回-123，因此在前面添加一個(gè)負(fù)號(hào)使其變?yōu)橄胍��?23。

但是，公式1并不可靠。因?yàn)槟承┳址�串可能�?huì)返回其他結(jié)果，例如單元格中的值為12JUN，那么：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

此時(shí)，會(huì)轉(zhuǎn)換為：

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

這是由于在強(qiáng)制將“12JUN”轉(zhuǎn)換成數(shù)字時(shí)，Excel認(rèn)為其是日期“2020-6-12”，因此將其轉(zhuǎn)換為相應(yīng)的序列數(shù)字。此時(shí)，LOOKUP函數(shù)返回-43994。

當(dāng)然，這不是唯一會(huì)出現(xiàn)這種情況的字符串，實(shí)際上，任何可以被Excel解釋為日期的字母數(shù)字都會(huì)如此，例如30SEP、01FEB等，這也會(huì)導(dǎo)致不正確的結(jié)果。

此外，公式1對(duì)于諸如123E3等形式的字符串也無(wú)效，其結(jié)果將是123000。因?yàn)樵谕ǔＧ闆r下，將123E3輸入單元格后，Excel會(huì)自動(dòng)將其轉(zhuǎn)換成科學(xué)計(jì)數(shù)格式。

LEFT與COUNT

公式2：

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

這是一個(gè)數(shù)組公式。

仍以單元格A1中的數(shù)據(jù)是“123ABC”為例。公式2可以轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

COUNT函數(shù)忽略錯(cuò)誤值，得到：

=0+LEFT(A1,3)

結(jié)果為：

123

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

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

=0+LEFT(A1,5)

轉(zhuǎn)換為：

=0+”123AB”

結(jié)果為：

#VALUE!

原因是字符串的末尾有其他數(shù)字，因此COUNT函數(shù)統(tǒng)計(jì)的數(shù)字個(gè)數(shù)大于字符串開(kāi)頭的數(shù)字個(gè)數(shù)，這樣LEFT取值仍是字母數(shù)字混合的字符串。

LEFT、MATCH與ISNUMBER

公式3：

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

這是一個(gè)數(shù)組公式。

仍以單元格A1中的數(shù)據(jù)為“123ABC”，公式3可轉(zhuǎn)換為：

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

對(duì)于ISNUMBER函數(shù)來(lái)說(shuō)，傳遞給它的如果是錯(cuò)誤值則返回FALSE，因此上述公式可轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

=0+LEFT(A1,4-1)

轉(zhuǎn)換為：

=0+LEFT(A1,3)

結(jié)果為：

123

在公式3中，MATCH/ISNUMBER組合確保字符串中除開(kāi)頭以外的數(shù)字不會(huì)影響最終的結(jié)果。例如如果單元格A1中的數(shù)據(jù)是“123ABC45”，那么公式3可以轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

=0+LEFT(A1,4-1)

轉(zhuǎn)換為：

=0+LEFT(A1,3)

結(jié)果為：

123

LEFT、MATCH與ISERR

與公式3的構(gòu)造一致，唯一的不同是使用ISERR函數(shù)代替了ISNUMBER函數(shù)，并強(qiáng)制返回由數(shù)字組成的數(shù)組。

公式4：

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

這是一個(gè)數(shù)組公式。

仍以單元格A1中的數(shù)據(jù)為“123ABC”，公式4可轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

=0+LEFT(A1,4-1)

轉(zhuǎn)換為：

=0+LEFT(A1,3)

結(jié)果為：

123

與公式3一樣，字符串中除開(kāi)頭有數(shù)字外其它部位存在數(shù)字時(shí)不影響結(jié)果。