Excel公式技巧:将所有数字提取到不同的单元格中

编辑：宝哥软件园来源：互联网时间：2021-09-28

新天龙八部

角色扮演 | 1.85G

评分

立即下载

研究了从字符串中提取所有数字的技术：

1.字符串由数字、字母和特殊字符组成。

2.数字在字符串中的任何位置。

3.字符串中的小数也被提取。

3.期望的结果是将所有数字返回到独立的单元格。

例如，单元格A1中的字符串：

81;8.75@5279@4.=45A？a；

B1单元：81。

细胞C1: 8.75。

D1手机：5279。

细胞E1: 4。

f1细胞：45。

解决办法

首先，确保活动单元格位于工作表第1行，然后定义以下两个名称。

名称：Arry1。

参考位置：=行(间接(“1:”len(“”$ a1“0”)-1))

名称：Arry2。

参考位置：=mmult(0(ABS(51.5-代码(mid(替换为(“”$ a1“0”、“/”、“”)，arry1 {0，1}，1)))) 6) * {2，1}，{1。1})

在单元格B1中输入数组公式：

=IFERROR(0 MID("" $A1 "0 "，1 SMALL(IF(Arry2=2，Arry1)，COLUMNS($A:A))、SUM(SMALL(IF(is number(MATCH(arr y2，{1，2}，0))、Arry1)，2*COLUMNS($A:A) {-1，0})*{-1，1}))，"")

向右拖放，直到出现一个空单元格。

原理分析

1.首先看看这两个定义的名字。对于Arry1:

=ROW(间接(" 1:"LEN(""$A1"0")-1))

生成整数数组。请注意，单元格A1的字符串前添加了一个非数字字符“”，末尾添加了一个非数字字符和一个数字“0”。为什么会这样处理？具体原因在后面详述。

上述公式转换为：

=ROW(INternet(" 1: " 27-1))

结果是：

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

2.对于Arry2:

=MMULT(0(ABS(51.5-CODE(MID(replace)(“”$ A1“0”/“/”，“”)，Arry1 {0，1}，1)))6)*{2，1}，{ 1；1})

(1)公式通过引用ASC字符代码来测试单元格A1中的数字。

(2)要识别数字子串，必须在串中找到两个不同的位置：一个对应数字的起始位置，另一个对应数字的结束位置。

(3)公式的第一部分将为MID函数提供参数start_num，生成的结果之间的减法将提供相应的参数num_chars。

(4)0-9范围内整数的ASC码为48-57，小数点为46。因此，如果先从字符串中排除与ASC 代码47对应的任何字符(“/”)，则可以确定字符串中ASC 代码46-57范围内的任何字符不是数字就是小数点。

(5)用字符的ASC码减去51.5，判断结果的绝对值。如果小于等于6，可以判断字符是数字还是小数点。(这里使用的技术相当于通常使用的两个单独的条件判断，即一个比较大于45的ASC 码，另一个比较小于58的ASC 码。)

(6)注意，为了找到数字的开始和结束位置，在这里搜索字符串中的两对字符：一对中的第一个字符是非数字字符，第二个字符是数字字符(提供数字字符串的开头)，另一对中的第一个字符是数字字符，第二个字符是非数字字符(提供数字字符串的结尾)。

(7)当然，如果字符串中的第一个或最后一个字符符合上述标准，则需要确保它们之前或之后有一些字符，这就是为什么我们在A1的开头和结尾连接适当的字符串。然后，还有你能看到的“”和“ 0”。

这样，Arry2公式转换为：

MMULT(0)(ABS(51.5-CODE(MID)(replace(81；8.75@5279@4.=45A？a；0、/、“”)、Arry1 {0，1}、1)))6)*{2，1}、{ 1；1})

转换为：

MMULT(0)(ABS(51.5-CODE(MID)”(81；8.75@5279@4.=45A？a；0 "，Arry1 {0，1}，1)))6)*{2，1}，{ 1；1})

替换Arry1以获得：

MMULT(0)(ABS(51.5-CODE(MID)”(81；8.75@5279@4.=45A？a；0",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24} {0,1},1)))6)*{2,1},{1;1})

接下来是值得关注的技术之一。因为您想考虑字符串中的字符对，所以您需要比较位置1的字符和位置2的字符，位置2的字符和位置3的字符，等等。为了实现这个目标，需要生成一个数组，作为参数start_num传递给MID函数：{1，2；2,3;3,4;4,5;5,6;…}。

因为Arry1是{ 1；2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}是一个24行1列的数组，它被添加到一个1行2列的数组{0，1}中，结果是一个24行2列的数组：

MMULT(0)(ABS(51.5-CODE(MID)”(81；8.75@5279@4.=45A？a；0",{1,2;2,3;3,4;4,5;5,6;6,7;7,8;8,9;9,10;10,11;11,12;12,13;13,14;14,15;15,16;16,17;17,18;18,19;19,20;20,21;21,22;22,23;23,24;24,25},1)))6)*{2,1},{1;1})

转换为：

MMULT(0)(ABS(51.5-CODE({“”，“8”；)8,"1; "1,"; "；"; ","8; "8,"."；".","7; "7,"5; "5,"";"","@";"@","5; "5,"2; "2,"7; "7,"9; "9,"@";"@","4; "4,"."；".","=";"=","4; "4,"5; "5,"";""、" A "；“一个”、“？”；"? "，“A”；“一”、“；”}))6)*{2,1},{1;1})

转换为：

MMULT(0)(ABS(51.5-{ 63，56；56,49;49,59;59,56;56,46;46,55;55,53;53,62;62,64;64,53;53,50;50,55;55,57;57,64;64,52;52,46;46,61;61,52;52,53;53,62;62,65;65,63;63,65;65,59})6)*{2,1},{1;1})

转换为：

MMULT(0 ({11.5，4.5；4.5,2.5;2.5,7.5;7.5,4.5;4.5,5.5;5.5,3.5;3.5,1.5;1.5,10.5;10.5,12.5;12.5,1.5;1.5,1.5;1.5,3.5;3.5,5.5;5.5,12.5;12.5,0.5;0.5,5.5;5.5,9.5;9.5,0.5;0.5,1.5;1.5,10.5;10.5,13.5;13.5,11.5;11.5,13.5;13.5,7.5}6)*{2,1},{1;1})

转换为：

MMULT({1，0；0,0;0,1;1,0;0,0;0,0;0,0;0,1;1,1;1,0;0,0;0,0;0,0;0,1;1,0;0,0;0,1;1,0;0,0;0,1;1,1;1,1;1,1;1,1}*{2,1},{1;1})

让我们看看这个数组中的值所代表的含义。让我们突出四组数字作为示例：

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

从MID函数获得的字符数组：

{"","8; "8,"1; "1",";"; "；","8;"8,"."; "。","7;"7,"5;"5,""; "","@"; "@","5;"5,"2;"2,"7;"7,"9;"9,"@"; "@","4;"4,"."; "。","="; "=","4;"4","5; "5,"";""、" A "；“一个”、“？”；"? "，“A”；“一”、“；”}

(1)第一个高亮显示的对是{0，1}，对应的字符是{“1”。}，因为“1”是数字和“；”号码

(2)第二个高亮显示的对是{1，1}，对应的字符是{ " "、“@”}，因为“”和“@”不是数字。

(3)第三个高亮对为{1，0}，对应字符为{“@”、“^ 4”}，由非数字和数字组成。

(4)第四对高亮显示的是{0，0}，对应的字符是{“4”、“5”}，都是数字。

现在我们需要一种方法来区分这四对，相当于：

{0，0}:这两对都是数字。

{1，0}:这对中的第一个是非数字，第二个是数字。

{0，1}:该对中的第一个是数字，第二个是非数字。

{1，1}:这两对都不是数字。

显然，我们对中间两对感兴趣，因为这告诉我们字符串中数字和非数字的交点。为此，由0/1组成的结果数组乘以由1行2列的两个元素(2和1)组成的数组。这样，公式转换为：

MMULT({2，0；0,0;0,1;2,0;0,0;0,0;0,0;0,1;2,1;2,0;0,0;0,0;0,0;0,1;2,0;0,0;0,1;2,0;0,0;0,1;2,1;2,1;2,1;2,1},{1;1})

获取：

{2;0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3}

数组中的0-3对应以上四对组合。例如，得到3的唯一方法是将1加2，乘以{2，1}后，由1和2组成的数组对为{1，1}，其中的值都是非数，所以3代表所有非数；值2来自{2，1}乘以{1，0}，它表示一个非数字后跟一个数字；1是从{2，1}乘以{0，1}得出的，表示一个数字后跟一个非数字，依此类推。

因此，Arry2之后生成的数组可以让我们知道字符串中的字符从数字变成非数字或者从非数字变成数字的位置。

3.现在看看单元格B1中的公式：

=IFERROR(0 MID("" $A1 "0 "，1 SMALL(IF(Arry2=2，Arry1)，COLUMNS($A:A))、SUM(SMALL(IF(is number(MATCH(arr y2，{1，2}，0))、Arry1)，2*COLUMNS($A:A) {-1，0})*{-1，1}))，"")

请看这里传递给MID函数的两个参数。要提取的字符串起始位置的参数start_num:

1小(IF(Arry2=2，Arry1)，COLUMNS($A:A))

如您所见，我们基于等于2的Arry2创建了一个数组，该数组对应于一对由非数字字符和数字字符组成的数组，即：

1小型(中频({ 2；0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3}=2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24})，COLUMNS(a : a))

转换为：

1小(如果({真；假；假；真；假；假；假；假；假；真；假；假；假；假；真；假；假；真；假；假；假；假；假；FALSE}，{ 1；2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24})，COLUMNS(a : a))

转换为：

1 SMAll({ 1；假；假；4;假；假；假；假；假；10;假；假；假；假；15;假；假；18;假；假；假；假；假；FALSE}，COLUMNS($A:A))

可以看出，生成的数组中的数值1、4、10、15和18是指定字符串中每个数字的起始位置。在B1中，COLUMNS函数返回1，公式可以转换为：

1 SMAll({ 1；假；假；4;假；假；假；假；假；10;假；假；假；假；15;假；假；18;假；假；假；假；假；FALSE}，1)

结果是(因为我们提前在A1中的字符串前添加了一个字符):

对于传递给MID函数以获取要提取的字符数的参数num_char:

SUM(SMALL(IF(is number(MATCH(arr y2，{1，2}，0))，Arry1)，2*COLUMNS($A:A) {-1，0})*{-1，1})

我们对数组2中的值1或2感兴趣，因为它们对应于字符串中的非数字/数字对。

为了确定每个提取的子串的长度，有必要计算每个连续的非数字/数字和数字/非数字间隔之间的字符数，因为它们代表每组连续数字的开始和结束位置。代入Arry2值后，上述公式转换为：

=SUM(SMALL(IF(is number(MATCH({ 2；0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3}、{1，2}、0))、Arry1)、2*COLUMNS($A:A) {-1，0})*{-1，1})

转换为：

=SUM(SMALL(IF(is number({ 2；#不适用；1;2;#不适用；#不适用；#不适用；1;#不适用；2;#不适用；#不适用；#不适用；1;2;#不适用；1;2;#不适用；1;#不适用；#不适用；#不适用；#N/A})，Arry1)，2*COLUMNS($A:A) {-1，0})*{-1，1})

转换为：

=SUM(SMALL(IF({ TRUE；假；真；真；假；假；假；真；假；真；假；假；假；真；真；假；真；真；假；真；假；假；假；FALSE}、Arry1)、2*COLUMNS($A:A) {-1，0})*{-1，1})

代入Arry1的值：

=SUM(SMALL(IF({ TRUE；假；真；真；假；假；假；真；假；真；假；假；假；真；真；假；真；真；假；真；假；假；假；FALSE}，{ 1；2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24})，2*COLUMNS($A:A) {-1，0})*{-1，1})

转换为：

=SUM(SMALL({ 1；假；3;4;假；假；假；8;假；10;假；假；假；14;15;假；17;18;假；20;假；假；假；FALSE}，2*COLUMNS($A:A) {-1，0})*{-1，1})

上面生成的数组中的值表示字符串中从非数字到数字或从数字到非数字的位置。

现在，我们需要指定SMALL函数的参数k。当我们向右拖动公式时，我们可以在相应的位置提取一对数字。第一对是第一和第二个值，即1和3；第二对是第三和第四个值，即4和8；等等。然后，从每对中的第二个值减去第一个值，得到所需的长度。因此，在B1中，公式可以转换为：

=SUM(SMALL({ 1；假；3;4;假；假；假；8;假；10;假；假；假；14;15;假；17;18;假；20;假；假；假；FALSE}，2*1 {-1，0})*{-1，1})

转换为：

=SUM(SMALL({ 1；假；3;4;假；假；假；8;假；10;假；假；假；14;15;假；17;18;假；20;假；假；假；FALSE}，{1，2})*{-1，1})

转换为：

=SUM({1，3}*{-1，1})

转换为：

=SUM({-1，3})

获取：