Excel公式技巧：一起定位字符串中的首個與最后一個數字

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

本文僅涉及被提取的字符串內包含唯一的數字子字符串的情況。

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

Account No. 1234567890: requires attention

顯然，我們要提取出1234567890。

下面是我們曾經使用的一個公式：

=-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函數生成的值的末尾添加“**0”，以保證能夠在任何情況下都得到正確的結果。例如，如果單元格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))))))

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

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

=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″))

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

好了！下面讓我們看看一個相似的例子，但要提取的子字符串數字中包含有分隔符：

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″))

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

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

在前面的一系列文章中，我們已經找到了一種非常合適的方法來確定字符串中第一個數字的位置，即MIN/FIND函數組合構造。然而，找到一種等效的用于確定字符串中最后一個數字的結構并不容易，能夠實現這一點是關鍵。

對于MID函數的參數num_chars：

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

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

公式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是數組公式。

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

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

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))

從而構成解決方案：

=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)))

不錯！但是，可以改進參數num_chars的構造嗎？

一種方法是對上面給出的公式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)))))

進行微小的調整。在2010年及以后的版本中，Excel提供了AGGREGATE函數，它不僅可使許多數組（CSE）結構轉換為非CSE，而且還具有標準的CSE公式無法復制的其他優點。

與公式3等價的使用AGGREGATE函數的公式為：

=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)

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

=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})

轉換為：

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

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

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

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

這類似于AGGREGATE的結構，將一個數組傳遞給其參數，得到兩個結果組成的數組。上面的公式轉換為：

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})

轉換為：

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})

轉換為：

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})

轉換為：

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})

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

{25,13}

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

=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}))