Excel技巧：从字符串提取末尾数字的三种方法

excel从字符串中提取数字数字位于字符串末尾

上一篇文章讲解了提取字符串开头数字的技术：

2这些连续的数字位于字符串的末尾

期望的结果是这些连续的数字在单个单元格中返回

与上一篇文章中一样，对于下面研究的每个解决方案，我们都需要在两种不同的情况下测试其合理性：

1字符串中除了末尾之外没有任何数字，例如ABC456。

2字符串中除了末尾、开头或中间以外还有数字，例如123ABC456或ABC123DEF456。

MIN和FIND

公式1：

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

如果单元格A1中的内容是“ABC456”，则上面的公式1返回“456”。
解析过程如下：

=0+MID("ABC456",MIN(FIND({0,1,2,3,4,5,6,7,8,9},"ABC456"&1/17)),LEN("ABC456"))

在A1后添加1/17与在其后添加字符串“0123456789”效果相同。
基本上，您需要将A1连接到包含0到9之间所有数字的字符串，以防止MIN函数返回错误。
因为如果A1不包含0到9的所有数字，那么FIND函数的返回值至少有一个是错误值，由于MIN函数不会忽略错误值，所以整个公式的结果也将是一个错误值。
通过首先在A1中的字符串末尾添加一个合适的值（例如1/17、3^45、“0123456789”），可以保证该字符串将包含0-9中的每个数字至少一个（以便FIND函数返回所有10个值（没有错误），确保我们将字符串放在A1的末尾不会影响MIN函数的所需输出。

因此，上面的公式现在转换为：

=0+MID("ABC456",MIN(FIND({0,1,2,3,4,5,6,7,8,9},”ABC456″&0.0588235294117647)),LEN("ABC456"))

你可能不知道，在某些公式结构中，MIN函数有无需按Ctrl+Shift+Enter组合键即可强制返回数组的能力。
。
本例就是这种情况，因此FIND函数将对传递给它的所有10个值起作用，而不仅仅是第一个值。
因此，上式转换为：

=0+MID("ABC456",MIN({7,19,13,14,4,5,6,21,11,17}),LEN("ABC456"))

MIN函数返回数字4在字符串中的起始位置，因此上式转换为：

=0+MID("ABC456",4,LEN("ABC456"))

请注意，MID函数（以及RIGHT和LEFT函数）有一些有用的功能properties，当传入的num_chars参数的值大于实际要输出的字符串长度时，不会出现错误。
也就是说，如果此参数的值会导致全部或部分返回值超出字符串末尾，则这些函数仅返回字符串末尾指定位置的那些字符。
这里，num_chars参数的值被指定为LEN(A1)，因为不存在子字符的长度大于字符串本身的情况。
这样，上式就转换为：

=0+MID(“ABC456″,4,6)

结果为：

456

让我们看看如果单元格A1中的字符串包含其他数字（例如123ABC456），公式1是否有效。
此时公式1转换为：

=0+MID("123ABC456",MIN(FIND({0,1,2,3,4,5,6,7,8,9},"123ABC4560.0588235294117647")),LEN(A1))

转换为：

=0+MID("123ABC456",MIN({10,1,2,3,7,8,9,24,14,20}),LEN(A1))

转换为：

=0+MID("123ABC456",1,LEN("123ABC456"))

转换为：

=0+MID(“123ABC456”,1,9)

由于字符串中的其他数字，找到的初始位置将传递给MID函数，因此结果为：

#VALUE！

LOOKUP和RIGHT

公式2：

=-LOOKUP(0,-RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

这个公式和上一个类似文章“Excel从字符串公式1提取数字-数字位于字符串开头的情况类似，只是使用RIGHT函数而不是LEFT函数

当然，如果单元格A1中的字符串类似于“JAN18”或“123E4”。
”，不会返回正确的结果，详细原因参见上一篇文章。

对、MATCH和NUMBER

公式3：

=0+RIGHT(A1,MATCH(FALSE,ISNUMBER(0+MID(A1,1+LEN(A1))-ROW(间接(“1:”&LEN(A1)))),1)),0)-1)

这是一个数组公式

如果单元格A1中的数据仍然是“ABC456”，则公式3可以转换为：

=0+RIGHT("ABC456",MATCH(FALSE,ISNUMBER(0+MID("ABC456",1+6-ROW(直接("1:"&6)),1))),0)-1)

转换为：

=0+RIGHT("ABC456",MATCH(FALSE,ISNUMBER(0+MID("ABC456",1+6-{1;2;3;4;5;6},1)),0)-1)

转换为(注意，这是将结果数组反转的方式，以便从末尾一一取出字符可以是字符串开头）：

=0+RIGHT("ABC456",MATCH(FALSE,ISNUMBER(0+MID("ABC456",{6;5;4;3;2;1},1)),0)-1)

转换为：

=0+Right("ABC456",MATCH(FALSE,ISNUMBER(0+{6;5;4;C;B;A}),0)-1)

强制使用数字字符通过动作转换为数字正确的数学运算（将数组增加0）：

=0+RIGHT("ABC456",MATCH(FALSE,ISNUMBER({6;5;4;#VALUE!;#VALUE!;#VALUE!}),0)-1)

ISNUMBER函数忽略在其参数中传递的错误值：

=0+Right("ABC456",MATCH(FALSE,{TRUEOF;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

转换in:

=0+RIGHT("ABC456",4-1)

4表示字符串“”中的第一个非数字字符是从e右边开始的。
左边出现的位置，减1表示数字字符出现的位置：

=0+RIGHT("ABC456",3)

结果为：

>

456

接下来我们看看字符串末尾以外的数字是否会影响最终的结果。
例如，如果单元格A1中的数据为“123ABC456”，则公式3可以转换为：

=0+RIGHT("123ABC456",MATCH(FALSE,ISNUMBER(0+MID(“123ABC456”,1+LEN(“123ABC456”)-行(间接(“1:”&LEN(“123ABC456”))),1)),0)-1)

转换为：

=0+RIGHT("123ABC456",MATCH(FALSE,ISNUMBER(0+{"6";"5";"4");"C";"B";"A";"3";"2";"1"}),0)-1)

转换为：

=0+RIGHT("123ABC456",匹配(假,数字(6;5;4;#VALUE!;#VALUE!;#VALUE!;3;2;1}),0)-1)

转换为：

=0+Right("123ABC456",MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE},0)-1)

转换为：

=0+RIGHT("ABC456",4-1)

4表示字符串“”中从右到左第一个非数字符出现的位置，减1表示数字字符出现in:

=0+Right("ABC456",3)

结果是：

456

如你所见，因为该数字是从右侧取出的左，字符字符串中其他位置的数字的存在不会影响结果。

RIGHT、MATCH和ISERR

公式4：

=0+RIGHT(A1,MATCH(1,0+ISERR(0+MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1))),1)),0)-1)

这是一个数组公式。

假设单元格A1中的数据为“123ABC456”，则公式4可转换为：

=0+RIGHT("123ABC456",MATCH(1,0+ISERR(0+{"6";"5";"4";"C";"B";"A";"3";"2";"1"}),0)-1)

转换为：

=0+RIGHT("123ABC456",MATCH(1,0+{FALSE;False;FalseSE},0)-1)

转换为：

=0+RIGHT("123ABC456",MATCH(1,{0;0;0;1;1;1;0;0;0},0)-1)

转换转换为：

=0+RIGHT(“123ABC456",4-1)

转换为：

=0+RIGHT("123ABC456",3)

结果为：

456

Excel提取字符串中的数字

单元格中的数据包含文本和数字（例如）。
如何使用公式提取单元格中的数字？先不要看答案，自己尝试一下。
该公式的思想是首先找到字符串文本中第一个数字出现的位置，然后从该位置检索所有数据。
公式数组公式为：=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1)),0),255)公式分析一、公式：MID(A1,ROW(1:10)),1)将单元格A1中的数据转换为数组：{"E";"x";"c";"e";"l";"2";"0”;“1”;“7”;“”}。
假设单元格A1中的数据长度不超过10个字符。
然后将数组乘以1。
即执行公式：1*MID(A1,ROW(1:10),1)，得到数组{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2;0;1;7;#VALUE!}。
接下来，使用IFERROR函数。
如果数组有错误值，则将其转换为TRUE，否则将转换为FALSE。
即，表达式ISERROR(1*MID(A1,ROW(1:10),1).))获取数组{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}。
使用MATCH函数查找数组中第一次出现FALSE。
即，使用MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1)),0)获取值6。
，从第6个字符开始显示数字。
此时的计算公式为=1*MID("Excel2017",6,256)，即从字符串“Excel2017”的第6位开始提取256个字符的数据，取出字符“2017”，并乘以1。
这将是。
并将其转换为数字。
总结MID功能可以让您实现数据分离。
将数字和文本相乘会产生不准确的值。
MATCH函数查找指定值的第一次出现。
数字乘以数字格式的文本并转换为数字。
下一期预览Excel公式练习18：获取每行中的第一个非空单元格如何使用公式获取每行中的第一个非空单元格例如，在下图所示的工作表中：您将需要使用公式根据上表查找每个项目的开始日期。
也就是说，我需要在B1:G1中查找与单元格区域B2:G6的每行中的第一个非空单元格相对应的日期。

如何从一个字符串中取取得其中的数字?

在处理包含字母和数字的数据时，有时我们需要提取其中的数字部分。
以Excel为例，假设A列包含几个由字母和数字组成的字符串，我们要从中提取数字。
首先，选择单元格C1并在公式栏中输入函数：=RIGHT(A1,6)。
这里用到了RIGHT函数，该函数用于从右侧删除指定数量的字符。
以A1单元格的数据为例，如果数据为“abc123456”，应用此函数后，将提取出“123456”。
完成上述步骤后，按Enter键，C1单元格将显示提取的数字。
然后，单击右下角的小滑块并将其向下拖动，以使用填充功能删除其他单元格中的数字。
这样，通过简单的Excel函数，我们就可以高效地从包含字母和数字的字符串中提取出数字，这对于数据清洗和处理来说非常重要。
值得注意的是，使用write函数时，需要根据实际数据长度调整提取的字符数。
如果单元格A1中的数据长度小于6，则提取的数字可能不完整。
因此，在实际操作中，应首先检查数据长度，以确保函数参数设置合适。
此外，我们还可以使用write函数以外的函数来完成此任务。
例如，您可以使用MID和LEN函数来精确定位和提取数字。
可以使用函数。
具体来说，可以先计算字母和数字之间的范围，然后使用MID函数提取数字部分。
这种方法提供了更大的灵活性，适合更复杂的数据处理要求。
这样我们就可以很方便地从包含字母和数字的字符串中提取出数字，可以为后续的数据分析和处理打下良好的基础。
这对于很多业务场景来说是一个非常实用的技能，可以帮助我们更高效地处理和分析数据。