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)