excel公式技巧：從字符串中提取數(shù)字——數(shù)字位于字符串末尾

excel公式技巧：從字符串中提取數(shù)字——數(shù)字位于字符串末尾

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

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

2. 這些連續(xù)的數(shù)字位于字符串的末尾

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

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

1. 字符串中除末尾外其他地方?jīng)]有數(shù)字的情況，例如ABC456。

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

MIN與FIND

公式1：

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1& 1/17)),LEN(A1))

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

=0+MID(“ABC456”,MIN(FIND({0,1,2,3,4,5,6,7,8,9},“ABC456” & 1/17)),LEN(“ABC456”))

在A1后面添加1/17與在其后面添加字符串“0123456789”效果相同，詳見《Excel公式技巧08. 17分之一和其他全數(shù)字(pandigitals)》。主要是必須將A1與包含0到9的所有數(shù)字的字符串連接起來，以防止MIN函數(shù)返回錯誤。因?yàn)槿绻鸄1不包含0-9的所有數(shù)字，那么FIND函數(shù)的返回值至少有一個是錯誤值，由于MIN函數(shù)不會忽略錯誤值，會導(dǎo)致整個公式的結(jié)果也是一個錯誤值。通過首先在A1中的字符串末尾添加一個適當(dāng)?shù)闹担ɡ?/17、3^45，“0123456789”），可以保證該字符串將包含至少一個0-9中的每個數(shù)字（因此FIND函數(shù)的所有10個返回值都是非錯誤值），同時通過將字符串放在A1的末尾確保我們不會影響MIN函數(shù)所需的輸出。

這樣，現(xiàn)在上述公式轉(zhuǎn)換為：

=0+MID(“ABC456”,MIN(FIND({0,1,2,3,4,5,6,7,8,9},”ABC456″&0.0588235294117647)),LEN(“ABC456”))

可能你還不知道，在某些公式結(jié)構(gòu)中，MIN函數(shù)具有強(qiáng)制返回數(shù)組的能力，且無需按Ctrl+Shift+Enter組合鍵。本例就屬于這種情況，因此FIND函數(shù)將對傳遞給它的所有10個值進(jìn)行操作，而不僅僅是對第一個值進(jìn)行操作。因此，上述公式轉(zhuǎn)換為：

=0+MID(“ABC456”,MIN({7,19,13,14,4,5,6,21,11,17}),LEN(“ABC456”))

MIN函數(shù)返回字符串中數(shù)字開始的位置4，因此，上述公式轉(zhuǎn)換為：

=0+MID(“ABC456”,4,LEN(“ABC456”))

注意，MID函數(shù)（以及RIGHT函數(shù)和LEFT函數(shù)）具有一些有用的特性，當(dāng)傳遞的參數(shù)num_chars的值大于要提取的實(shí)際字符串的長度時不會出錯。也就是說，如果該參數(shù)的值將導(dǎo)致全部或部分返回值超出字符串末尾的情況時，則這些函數(shù)僅返回從指定位置到字符串末尾的那些字符。這里將參數(shù)num_chars的值指定為LEN(A1)，因?yàn)闆]有子字符串的長度會大于字符串自身的情況。這樣，上述公式轉(zhuǎn)換為：

=0+MID(“ABC456″,4,6)

得到結(jié)果：

456

下面我們看看如果單元格A1中的字符串包含其它數(shù)字例如123ABC456，公式1是否有效。此時公式1轉(zhuǎn)換為：

=0+MID(“123ABC456“,MIN(FIND({0,1,2,3,4,5,6,7,8,9},”123ABC4560.0588235294117647”)),LEN(A1))

轉(zhuǎn)換為：

=0+MID(“123ABC456“,MIN({10,1,2,3,7,8,9,24,14,20}),LEN(A1))

轉(zhuǎn)換為：

=0+MID(“123ABC456“,1,LEN(“123ABC456“))

轉(zhuǎn)換為：

=0+MID(“123ABC456“,1,9)

由于字符串中有其它數(shù)字的影響，導(dǎo)致找到的傳遞到MID函數(shù)的起始位置變化，因此結(jié)果為：

#VALUE!

LOOKUP與RIGHT

公式2：

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

這個公式與上一篇文章《Excel公式技巧10：從字符串中提取數(shù)字——數(shù)字位于字符串開頭》中的公式1相似，只是使用RIGHT函數(shù)代替了LEFT函數(shù)。

當(dāng)然，如果單元格A1中的字符串與“JAN18”、“123E4”類似，則不會返回正確的結(jié)果，詳細(xì)原因參見上篇文章。

RIGHT、MATCH與ISNUMBER

公式3：

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

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

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

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,1+6-ROW(INDIRECT(“1:”& 6)),1)),0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,1+6-{1;2;3;4;5;6},1)),0)-1)

轉(zhuǎn)換為（注意，這里是如何將得到的數(shù)組顛倒，從而使得從字符串的末尾到開頭逐個獲取字符成為可能）：

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,{6;5;4;3;2;1},1)),0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+{6;5;4;C;B;A}),0)-1)

通過適當(dāng)?shù)臄?shù)學(xué)運(yùn)算（將數(shù)組與0相加），強(qiáng)制數(shù)字字符轉(zhuǎn)換成數(shù)字：

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER({6;5;4;#VALUE!;#VALUE!;#VALUE!}),0)-1)

ISNUMBER函數(shù)忽略傳遞給其參數(shù)中的錯誤值：

=0+RIGHT(“ABC456”,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“ABC456”,4-1)

其中的4表示在字符串“”中第一個非數(shù)字字符自右向左出現(xiàn)的位置，減1表示數(shù)字字符出現(xiàn)的位置：

=0+RIGHT(“ABC456”,3)

結(jié)果為：

456

下面，我們來看看字符串中除末尾以外的數(shù)字會不會影響最終的結(jié)果。例如如果單元格A1中的數(shù)據(jù)是“123ABC456”，那么公式3可以轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“123ABC456”,1+LEN(“123ABC456”)-ROW(INDIRECT(“1:”& LEN(“123ABC456”))),1)),0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(0+{“6”;”5”;”4”;”C”;”B”;”A”;”3”;”2”;”1”}),0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(6;5;4;#VALUE!; #VALUE!; #VALUE!;3;2;1}),0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE},0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“ABC456”,4-1)

其中的4表示在字符串“”中第一個非數(shù)字字符自右向左出現(xiàn)的位置，減1表示數(shù)字字符出現(xiàn)的位置：

=0+RIGHT(“ABC456”,3)

結(jié)果為：

456

可以看到，由于是從右向左取數(shù)，因此在字符串的其它位置存在數(shù)字并不會影響結(jié)果。

RIGHT、MATCH與ISERR

公式4：

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

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

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

=0+RIGHT(“123ABC456”,MATCH(1,0+ISERR(0+{“6″;”5″;”4″;”C”;”B”;”A”;”3″;”2″;”1″}),0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,MATCH(1,0+{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,MATCH(1,{0;0;0;1;1;1;0;0;0},0)-1)

轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,4-1)

轉(zhuǎn)換為：

=0+RIGHT(“123ABC456”,3)

結(jié)果為：

456

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

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。