excel怎么同時定位字符串中的第一個和最后一個數(shù)字

excel怎么同時定位字符串中的第一個和最后一個數(shù)字

在很多情況下，我們都面臨著需要確定字符串中第一個和最后一個數(shù)字的位置的問題，這可能是為了提取包圍在這兩個邊界內(nèi)的子字符串。然而，通常的公式都是針對所需提取的子字符串完全由數(shù)字組成，如果要提取的數(shù)字中有分隔符（例如電話號碼）則無法使用。當(dāng)然，可以先執(zhí)行替換操作來去掉字符串中的分隔符，這可能會更復(fù)雜些。

本文僅涉及被提取的字符串內(nèi)包含唯一的數(shù)字子字符串的情況。

我們以示例來解。先看一下要提取的數(shù)字中沒有分隔符的情形，例如在單元格A1中的字符串如下：

Account No. 1234567890: requires attention

顯然，我們要提取出1234567890。

下面是我們曾經(jīng)使用的一個公式：

=-LOOKUP(1,-(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))

注意，必須在MID函數(shù)生成的值的末尾添加“**0”，以保證能夠在任何情況下都得到正確的結(jié)果。例如，如果單元格A1中的字符串是：

Account No. 12-Jun: requires attention

使用沒有添加“**0”的公式：

=-LOOKUP(1,-(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))))

返回的結(jié)果不是12，而是43994，即日期2020-6-12對應(yīng)的序數(shù)。連接字符串“** 0”后，確保類似于“12–Jun”的字符串變?yōu)椤?2–Jun**0”，這樣Excel不會將它們認為數(shù)字。同樣，這也適用于與科學(xué)記數(shù)法格式的數(shù)字相似的字符串。

當(dāng)然，這樣的字符串還必須具有使任何數(shù)字保持不變的特性。字符串“**0”等效于“E0”，即表示索引為0的科學(xué)計數(shù)法，與10 ^ 0一致，因此可保證以這種形式表示的任何數(shù)字都將是不變。可以在工作表中進行下列測試來驗證：

=0+(147&”**0″)

返回147。

=0+(147&”**2″)

返回14700。

這種方式比“E0”更好，例如：

=-LOOKUP(1,-(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))&”E0″))

得到的結(jié)果是36689，因為提取的子字符串為“12-JunE0”，Excel認為是日期2000-6-12。并且，“E”在不同的環(huán)境中可能有不同的解釋。

好了！下面讓我們看看一個相似的例子，但要提取的子字符串?dāng)?shù)字中包含有分隔符：

Account No. 1-234-5678-90: requiresattention

使用上面給出的公式：

=-LOOKUP(1,-(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))

返回1，而不是我們想要的1-234-5678-90。

正如上文提出的，先刪除分隔符并不是一件簡單的事：

=-LOOKUP(1,-(MID(SUBSTITUTE(A1,”-“,””),MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))

乍一看似乎可以，但返回的結(jié)果是1234567890。留給我們的是，如何在正確的位置重新插入分隔符？當(dāng)然，如果所給字符串的格式是固定的，例如電話號碼。然而，即便如此，使用多個REPLACE/SUBSTITUTE函數(shù)可能使公式更復(fù)雜。

本文尋找的是如何通過確定字符串中的第一個和最后一個數(shù)字來提取出子字符串的一種通用解決方案，而不管分隔符是什么、有多少，并且不需要執(zhí)行替換操作。

在前面的一系列文章中，我們已經(jīng)找到了一種非常合適的方法來確定字符串中第一個數(shù)字的位置，即MIN/FIND函數(shù)組合構(gòu)造。然而，找到一種等效的用于確定字符串中最后一個數(shù)字的結(jié)構(gòu)并不容易，能夠?qū)崿F(xiàn)這一點是關(guān)鍵。

對于MID函數(shù)的參數(shù)num_chars：

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

假設(shè)希望避免[some construction]由兩個單獨的子句進行減法運算，其中一個是字符串內(nèi)第一個數(shù)字的位置，另一個是最后一個數(shù)字的位置。我們首先查看一些確定字符串中最后一個數(shù)字的位置的公式結(jié)構(gòu)，然后查看其中的哪一個（如果有的話）也可能有助于發(fā)現(xiàn)第一個數(shù)字的位置，這可能會很有用。下面是實現(xiàn)此目的的3種主要的公式結(jié)構(gòu)：

公式1：

=MATCH(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))

公式2：

=LOOKUP(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))

公式3：

=MAX(IF(ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

其中，公式1和公式3是數(shù)組公式。

顯然，最好的是公式1，因為它不需要重復(fù)ROW子句。但是，這樣的構(gòu)造還可以用于查找字符串中的第一個數(shù)字嗎？如果不行，公式2可以嗎？公式3呢？

我們先嘗試減法運行，即使用確定最后一個數(shù)字位置的子句減去用于確定第一個數(shù)字位置的子句：

MATCH(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17))

從而構(gòu)成解決方案：

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),1+MATCH(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)))

不錯！但是，可以改進參數(shù)num_chars的構(gòu)造嗎？

一種方法是對上面給出的公式3：

=MAX(IF(ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

進行微小的調(diào)整。在2010年及以后的版本中，Excel提供了AGGREGATE函數(shù)，它不僅可使許多數(shù)組（CSE）結(jié)構(gòu)轉(zhuǎn)換為非CSE，而且還具有標(biāo)準(zhǔn)的CSE公式無法復(fù)制的其他優(yōu)點。

與公式3等價的使用AGGREGATE函數(shù)的公式為：

=AGGREGATE(14,6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),1)

我們可以利用其來不只生成最大值或最小值，而是生成包含這兩個值的數(shù)組。因此，構(gòu)造公式：

=MMULT(AGGREGATE({14,15},6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),1),{1;-1})

轉(zhuǎn)換為：

=MMULT({25,13},{1;-1})

其中的13和25分別代表字符串中第一個和最后一個數(shù)字的位置。

但是，其仍有一點缺陷，就是需要重復(fù)ROW結(jié)構(gòu)。我們能否對此進行改進，找到不需要重復(fù)子句的公式構(gòu)造？是的，可以使用：

MATCH(“*”,T(1/(1+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))),{1,0})

這類似于AGGREGATE的結(jié)構(gòu)，將一個數(shù)組傳遞給其參數(shù)，得到兩個結(jié)果組成的數(shù)組。上面的公式轉(zhuǎn)換為：

MATCH(“*”,T(1/(1+{“A”;”c”;”c”;”o”;”u”;”n”;”t”;””;”N”;”o”;”.”;””;”1″;”-“;”2″;”3″;”4″;”-“;”5″;”6″;”7″;”8″;”-“;”9″;”0″;””;”r”;”e”;”q”;”u”;”i”;”r”;”e”;”s”;””;”a”;”t”;”t”;”e”;”n”;”t”;”i”;”o”;”n”})),{1,0})

轉(zhuǎn)換為：

MATCH(“*”,T(1/({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2;#VALUE!;3;4;5;#VALUE!;6;7;8;9;#VALUE!;10;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})),{1,0})

轉(zhuǎn)換為：

MATCH(“*”,T({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0.5;#VALUE!;0.333333333333333;0.25;0.2;#VALUE!;0.166666666666667;0.142857142857143;0.125;0.111111111111111;#VALUE!;0.1;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}),{1,0})

轉(zhuǎn)換為：

MATCH(“*”,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;””;#VALUE!;””;””;””;#VALUE!;””;””;””;””;#VALUE!;””;””;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},{1,0})

可以看出，不是錯誤值的就是數(shù)字值。指定參數(shù)match_type的值為1將為提供數(shù)組中最后一個非#VALUE!的位置；為0將提供第一個非#VALUE!的位置。這樣，上面公式轉(zhuǎn)換成：

{25,13}

現(xiàn)在，可以將此數(shù)組傳遞給MMULT函數(shù)，以最終得出MID函數(shù)的參數(shù)num_chars參數(shù)的值。最終的公式為：

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),1+MMULT(MATCH(“*”,T(1/(1+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))),{1,0}),{1;-1}))

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