excel公式技能提取指定长度的连续数字子串-英雄云拓展知识分享

本文给出了一种从可能包括若干个不同长度的数字的字符串中提取指定长度的数字的解决方案。在实际的工作表中，存在着许多此类需求，例如从字符串中获得6位数字账号。

下面是一个示例：

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

从该字符串中提取出现的一个6位数字（123456）。

在字符串中正肯定位一个6位数字，需要斟酌在与任意6个连续数字的字符串相邻的之前和以后的字符，并验证这两个字符都不是数字。

在这里，将介绍两类解决方案，第一种是静态的，要提取的数字长度是固定的；第2种是动态的，允许长度变化。

假定字符串在单元格A1中，则公式为：

=0+MID(“ζ”&A1&”ζ”,1+MATCH(26,MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)⑸)),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)⑸))

这是我们很熟习的一个结构，简单地生成一个整数数组，该数组由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)⑸)),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)⑸)),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函数的数组正交。

在取得了由子字符串中的每一个单独字符组成的数组以后，需要查询每一个字符组肯定其第一个和最后一个字符是不是为非数字字符，中间的6个字符是不是为6个数字。可使用例如 ISNUMBER到达这个目的，这里选择的ISERR一样出色，由于当尝试强迫转换为数字时（例如这里使用+0），唯一不会致使毛病的单字符字符串就是从0到9的数字。因此：

N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)⑸)),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}

现在，到了关键部份。回想一下，我们正在搜索一次出现的6个数字，该数字的两端都由一个非数字包围。在上面的数组中，这类情况将被视为字符串：

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

为了标识此0和1序列的位置，可使用MMULT函数。这里要选择一个数组与上述矩阵中94个组中的每组8个值相乘，确保乘积唯一。由于我们知道94组值中每组中的每一个值只能是0或1，所以只需要确保在选择MMULT函数的第2个矩阵时不会致使其他任何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)⑸)),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)⑸)),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个字符中的第一个字符是非数字，所以需要向右移动一个位置，即想要得到的6位数数字的开始。因此：

=0+MID(“ζ”&A1&”ζ”,1+MATCH(26,MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)⑸)),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)