Excel公式技巧：從字符串中提取指定長度的連續數字子串

本文給出了一種從可能包含若干個不同長度的數字的字符串中提取指定長度的數字的解決方案。在實際的工作表中，存在著許多此類需求，例如從字符串中獲取6位數字賬號。

下面是一個示例：

20/04/15 – VAT Reg: 1234567: Please send123456 against Order #98765, Customer Code A123XY, ￡125.00

從該字符串中提取出現的一個6位數字(123456)。

在字符串中正確定位一個6位數字，需要考慮在與任意6個連續數字的字符串相鄰的之前和之后的字符，并驗證這兩個字符都不是數字。

在這里，將介紹兩種解決方案，第一種是靜態的，要提取的數字長度是固定的;第二種是動態的，允許長度變化。

假設字符串在單元格A1中，則公式為：

=0+MID(“ζ”&A1&”ζ”,1+MATCH(26,MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),{1,2,3,4,5,6,7,8},1))),{13;1;1;1;1;1;1;13}),0),6)

先看看公式中的：

ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5))

這是我們很熟悉的一個結構，簡單地生成一個整數數組，該數組由1到A1中字符串長度(此處為99)減去5的值組成，即：

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94}

這個值(此處為5)的選擇必須比我們要提取的數字的長度小1，因為這意味著當我們將此數組傳遞給MID函數作為其參數start_num的值時，確保將考慮A1中字符串長度為8的所有子字符串。

但是，這里使用5而不是7的原因是，必須確保首先在字符串的開頭和結尾連接單個非數字字符。由于解決方案的關鍵之處在于有效地測試所有長度為8個字符的子字符串，并驗證其中的子字符串依次由1個非數字、6個數字和1個非數字組成。對于6個數字處于字符串的開頭或結尾的情況，進行適當調整。這樣：

MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8)

轉換為：

MID(“ζ20/04/15 – VAT Reg: 1234567: Please send 123456 againstOrder #98765, Customer Code A123XY, ￡125.00ζ”,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94},8)

得到：

{“ζ20/04/1″;”20/04/15″;”0/04/15”;”/04/15 -“;”04/15 – “;”4/15 – V”;”/15- VA”;”15 – VAT”;”5 – VAT “;” – VATR”;”- VAT Re”;” VAT Reg”;”VAT Reg:”;”ATReg: “;”T Reg: 1″;” Reg: 12″;”Reg:123″;”eg: 1234″;”g: 12345″;”: 123456″;”1234567″;”1234567:”;”234567: “;”34567:P”;”4567: Pl”;”567: Ple”;”67: Plea”;”7:Pleas”;”: Please”;” Please “;”Pleases”;”lease se”;”ease sen”;”ase send”;”sesend “;”e send 1″;” send 12″;”send123″;”end 1234″;”nd 12345″;”d 123456″;”123456 “;”123456 a”;”23456 ag”;”3456aga”;”456 agai”;”56 again”;”6 agains”;”against”;”against “;”gainst O”;”ainstOr”;”inst Ord”;”nst Orde”;”st Order”;”tOrder “;” Order #”;”Order #9″;”rder#98″;”der #987″;”er #9876″;”r #98765″;”#98765,”;”#98765, “;”98765, C”;”8765,Cu”;”765, Cus”;”65, Cust”;”5, Custo”;”,Custom”;” Custome”;”Customer”;”ustomer”;”stomer C”;”tomer Co”;”omer Cod”;”merCode”;”er Code “;”r Code A”;” CodeA1″;”Code A12″;”ode A123″;”de A123X”;”eA123XY”;” A123XY,”;”A123XY, “;”123XY,￡”;”23XY, ￡1″;”3XY, ￡12″;”XY, ￡125″;”Y,￡125.”;”, ￡125.0″;” ￡125.00″;”￡125.00ζ”}

將上述數組傳遞到外圍的MID函數，分析數組中由8個字符組成的每個元素：

MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),{1,2,3,4,5,6,7,8},1)

轉換為：

{“ζ”,”2″,”0″,”/”,”0″,”4″,”/”,”1″;”2″,”0″,”/”,”0″,”4″,”/”,”1″,”5″;”0″,”/”,”0″,”4″,”/”,”1″,”5″,””;”/”,”0″,”4″,”/”,”1″,”5″,””,”-“;”0″,”4″,”/”,”1″,”5″,””,”-“,””;”4″,”/”,”1″,”5″,””,”-“,””,”V”;”/”,”1″,”5″,””,”-“,””,”V”,”A”;”1″,”5″,””,”-“,” “,”V”,”A”,”T”;”5″,””,”-“,””,”V”,”A”,”T”,” “;””,”-“,””,”V”,”A”,”T”,” “,”R”;”-“,””,”V”,”A”,”T”,””,”R”,”e”;” “,”V”,”A”,”T”,””,”R”,”e”,”g”;”V”,”A”,”T”,””,”R”,”e”,”g”,”:”;”A”,”T”,””,”R”,”e”,”g”,”:”,””;”T”,””,”R”,”e”,”g”,”:”,””,”1″;””,”R”,”e”,”g”,”:”,” “,”1″,”2″;”R”,”e”,”g”,”:”,””,”1″,”2″,”3″;”e”,”g”,”:”,””,”1″,”2″,”3″,”4″;”g”,”:”,””,”1″,”2″,”3″,”4″,”5″;”:”,””,”1″,”2″,”3″,”4″,”5″,”6″;””,”1″,”2″,”3″,”4″,”5″,”6″,”7″;”1″,”2″,”3″,”4″,”5″,”6″,”7″,”:”;”2″,”3″,”4″,”5″,”6″,”7″,”:”,””;”3″,”4″,”5″,”6″,”7″,”:”,””,”P”;”4″,”5″,”6″,”7″,”:”,””,”P”,”l”;”5″,”6″,”7″,”:”,””,”P”,”l”,”e”;”6″,”7″,”:”,””,”P”,”l”,”e”,”a”;”7″,”:”,””,”P”,”l”,”e”,”a”,”s”;”:”,””,”P”,”l”,”e”,”a”,”s”,”e”;””,”P”,”l”,”e”,”a”,”s”,”e”,””;”P”,”l”,”e”,”a”,”s”,”e”,””,”s”;”l”,”e”,”a”,”s”,”e”,””,”s”,”e”;”e”,”a”,”s”,”e”,””,”s”,”e”,”n”;”a”,”s”,”e”,””,”s”,”e”,”n”,”d”;”s”,”e”,””,”s”,”e”,”n”,”d”,””;”e”,” “,”s”,”e”,”n”,”d”,””,”1″;””,”s”,”e”,”n”,”d”,””,”1″,”2″;”s”,”e”,”n”,”d”,””,”1″,”2″,”3″;”e”,”n”,”d”,””,”1″,”2″,”3″,”4″;”n”,”d”,””,”1″,”2″,”3″,”4″,”5″;”d”,””,”1″,”2″,”3″,”4″,”5″,”6″;””,”1″,”2″,”3″,”4″,”5″,”6″,””;”1″,”2″,”3″,”4″,”5″,”6″,””,”a”;”2″,”3″,”4″,”5″,”6″,””,”a”,”g”;”3″,”4″,”5″,”6″,””,”a”,”g”,”a”;”4″,”5″,”6″,””,”a”,”g”,”a”,”i”;”5″,”6″,””,”a”,”g”,”a”,”i”,”n”;”6″,””,”a”,”g”,”a”,”i”,”n”,”s”;””,”a”,”g”,”a”,”i”,”n”,”s”,”t”;”a”,”g”,”a”,”i”,”n”,”s”,”t”,””;”g”,”a”,”i”,”n”,”s”,”t”,””,”O”;”a”,”i”,”n”,”s”,”t”,””,”O”,”r”;”i”,”n”,”s”,”t”,””,”O”,”r”,”d”;”n”,”s”,”t”,””,”O”,”r”,”d”,”e”;”s”,”t”,””,”O”,”r”,”d”,”e”,”r”;”t”,””,”O”,”r”,”d”,”e”,”r”,””;””,”O”,”r”,”d”,”e”,”r”,””,”#”;”O”,”r”,”d”,”e”,”r”,””,”#”,”9″;”r”,”d”,”e”,”r”,””,”#”,”9″,”8″;”d”,”e”,”r”,””,”#”,”9″,”8″,”7″;”e”,”r”,””,”#”,”9″,”8″,”7″,”6″;”r”,””,”#”,”9″,”8″,”7″,”6″,”5″;””,”#”,”9″,”8″,”7″,”6″,”5″,”,”;”#”,”9″,”8″,”7″,”6″,”5″,”,”,””;”9″,”8″,”7″,”6″,”5″,”,”,””,”C”;”8″,”7″,”6″,”5″,”,”,””,”C”,”u”;”7″,”6″,”5″,”,”,””,”C”,”u”,”s”;”6″,”5″,”,”,””,”C”,”u”,”s”,”t”;”5″,”,”,””,”C”,”u”,”s”,”t”,”o”;”,”,””,”C”,”u”,”s”,”t”,”o”,”m”;””,”C”,”u”,”s”,”t”,”o”,”m”,”e”;”C”,”u”,”s”,”t”,”o”,”m”,”e”,”r”;”u”,”s”,”t”,”o”,”m”,”e”,”r”,””;”s”,”t”,”o”,”m”,”e”,”r”,””,”C”;”t”,”o”,”m”,”e”,”r”,””,”C”,”o”;”o”,”m”,”e”,”r”,””,”C”,”o”,”d”;”m”,”e”,”r”,””,”C”,”o”,”d”,”e”;”e”,”r”,””,”C”,”o”,”d”,”e”,””;”r”,””,”C”,”o”,”d”,”e”,””,”A”;””,”C”,”o”,”d”,”e”,” “,”A”,”1″;”C”,”o”,”d”,”e”,””,”A”,”1″,”2″;”o”,”d”,”e”,””,”A”,”1″,”2″,”3″;”d”,”e”,””,”A”,”1″,”2″,”3″,”X”;”e”,””,”A”,”1″,”2″,”3″,”X”,”Y”;””,”A”,”1″,”2″,”3″,”X”,”Y”,”,”;”A”,”1″,”2″,”3″,”X”,”Y”,”,”,””;”1″,”2″,”3″,”X”,”Y”,”,”,””,”￡”;”2″,”3″,”X”,”Y”,”,”,””,”￡”,”1″;”3″,”X”,”Y”,”,”,””,”￡”,”1″,”2″;”X”,”Y”,”,”,””,”￡”,”1″,”2″,”5″;”Y”,”,”,””,”￡”,”1″,”2″,”5″,”.”;”,”,””,”￡”,”1″,”2″,”5″,”.”,”0″;””,”￡”,”1″,”2″,”5″,”.”,”0″,”0″;”￡”,”1″,”2″,”5″,”.”,”0″,”0″,”ζ”}

注意到，傳遞給MID函數的參數start_num的值為：

{1,2,3,4,5,6,7,8}

與作為其參數text的值傳遞給MID函數的數組正交。

在獲得了由子字符串中的每個單獨字符組成的數組之后，需要查詢每個字符組確定其第一個和最后一個字符是否為非數字字符，中間的六個字符是否為六個數字。可以使用例如 ISNUMBER達到這個目的，這里選擇的ISERR同樣出色，因為當嘗試強制轉換為數字時(例如這里使用+0)，唯一不會導致錯誤的單字符字符串就是從0到9的數字。因此：

N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),{1,2,3,4,5,6,7,8},1)))

轉換為：

{1,0,0,1,0,0,1,0;0,0,1,0,0,1,0,0;0,1,0,0,1,0,0,1;1,0,0,1,0,0,1,1;0,0,1,0,0,1,1,1;0,1,0,0,1,1,1,1;1,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,0;1,1,1,0,0,0,0,0;1,1,0,0,0,0,0,0;1,0,0,0,0,0,0,0;0,0,0,0,0,0,0,1;0,0,0,0,0,0,1,1;0,0,0,0,0,1,1,1;0,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,0;1,1,1,0,0,0,0,0;1,1,0,0,0,0,0,0;1,0,0,0,0,0,0,1;0,0,0,0,0,0,1,1;0,0,0,0,0,1,1,1;0,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,0;1,1,1,0,0,0,0,0;1,1,0,0,0,0,0,1;1,0,0,0,0,0,1,1;0,0,0,0,0,1,1,1;0,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,1;1,1,1,0,0,0,1,1;1,1,0,0,0,1,1,1;1,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,0;0,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,1;1,1,1,0,0,0,1,0;1,1,0,0,0,1,0,0;1,0,0,0,1,0,0,1}

現在，到了關鍵部分。回想一下，我們正在搜索一次出現的六個數字，該數字的兩端都由一個非數字包圍。在上面的數組中，這種情況將被視為字符串：

{1,0,0,0,0,0,0,1}

為了標識此0和1序列的位置，可以使用MMULT函數。這里要選擇一個數組與上述矩陣中94個組中的每組8個值相乘，確保乘積唯一。因為我們知道94組值中每組中的每個值只能是0或1，所以只需要確保在選擇MMULT函數的第二個矩陣時不會導致其他任何1和0置換后的組合得到與所需提取的相對應的數組相乘時獲得的值相同。這里選擇的數組是：

{13;1;1;1;1;1;1;13}

我們先看看下面的矩陣運算：

=MMULT({1,0,0,0,0,0,0,1},{13;1;1;1;1;1;1;13})

結果是26，即：

(1×13)+(0x1)+(0x1)+(0x1)+(0x1)+(0x1)+(0x1)+(1×13)

因此，結果是26所對應的一組數組即為我們想要的數組。

這樣：

MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),{1,2,3,4,5,6,7,8},1))),{13;1;1;1;1;1;1;13})

轉換為：

MMULT({1,0,0,1,0,0,1,0;0,0,1,0,0,1,0,0;0,1,0,0,1,0,0,1;1,0,0,1,0,0,1,1;0,0,1,0,0,1,1,1;0,1,0,0,1,1,1,1;1,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,0;1,1,1,0,0,0,0,0;1,1,0,0,0,0,0,0;1,0,0,0,0,0,0,0;0,0,0,0,0,0,0,1;0,0,0,0,0,0,1,1;0,0,0,0,0,1,1,1;0,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,0;1,1,1,0,0,0,0,0;1,1,0,0,0,0,0,0;1,0,0,0,0,0,0,1;0,0,0,0,0,0,1,1;0,0,0,0,0,1,1,1;0,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,0;1,1,1,0,0,0,0,0;1,1,0,0,0,0,0,1;1,0,0,0,0,0,1,1;0,0,0,0,0,1,1,1;0,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,1;0,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0;1,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,1;1,1,1,0,0,0,1,1;1,1,0,0,0,1,1,1;1,0,0,0,1,1,1,1;0,0,0,1,1,1,1,1;0,0,1,1,1,1,1,0;0,1,1,1,1,1,0,0;1,1,1,1,1,0,0,0;1,1,1,1,0,0,0,1;1,1,1,0,0,0,1,0;1,1,0,0,0,1,0,0;1,0,0,0,1,0,0,1},{13;1;1;1;1;1;1;13})

得到：

{15;2;15;28;16;17;30;18;19;32;32;32;32;32;19;18;17;16;15;14;13;13;14;15;16;17;18;19;32;32;32;32;32;32;32;19;18;17;16;15;14;26;14;15;16;17;18;19;32;32;32;32;32;32;32;32;32;19;18;17;16;15;27;27;15;16;17;18;19;32;32;32;32;32;32;32;32;32;32;19;18;17;29;29;29;29;17;5;5;17;29;16;15;27}

將該數組傳遞給MATCH函數，并使用26作為其參數lookup_value的值：

MATCH(26,MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),{1,2,3,4,5,6,7,8},1))),{13;1;1;1;1;1;1;13}),0)

轉換為：

MATCH(26,{15;2;15;28;16;17;30;18;19;32;32;32;32;32;19;18;17;16;15;14;13;13;14;15;16;17;18;19;32;32;32;32;32;32;32;19;18;17;16;15;14;26;14;15;16;17;18;19;32;32;32;32;32;32;32;32;32;19;18;17;16;15;27;27;15;16;17;18;19;32;32;32;32;32;32;32;32;32;32;19;18;17;29;29;29;29;17;5;5;17;29;16;15;27},0)

得到：

42

這與我們要查找的8個字符中的第一個字符在字符串中的位置相對應，注意這8個字符中的第一個字符是非數字，所以需要向右移動一個位置，即想要得到的六位數數字的開始。因此：

=0+MID(“ζ”&A1&”ζ”,1+MATCH(26,MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),{1,2,3,4,5,6,7,8},1))),{13;1;1;1;1;1;1;13}),0),6)

轉換為：

=0+MID(“ζ20/04/15 – VAT Reg:1234567: Please send 123456 against Order #98765, Customer Code A123XY,￡125.00ζ”,1+42,6)

轉換為：

=0+MID(“ζ20/04/15 – VAT Reg: 1234567:Please send 123456 against Order #98765, Customer Code A123XY, ￡125.00ζ”,43,6)

轉換為：

=0+”123456″

得到：

123456

最后，給出本解決方案的公式的動態版本，允許將要查找的數字的長度作為變量。假設在在B1中指定要提取的數字的長度，則公式為：

=0+MID(“ζ”&A1&”ζ”,1+MATCH(4*B1,MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-B1+1)),B1+2),COLUMN(INDEX(1:1,1):INDEX(1:1,B1+2)),1))),(B1*2)^ISNUMBER(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,B1+2)),(B1+2)^{0,1},0))),0),B1)