如何提取字母数字字符串的数字部分。例如:如果单元格A1 包含的是字符串“abc123”,则将值 123返回单元格 B1 中。
此解决方案的基本原理是搜索并返回字母数字字符串中的第一个数字,然后只返回其后的数字。
此解决方案包括创建公式以完成下列任务:
我们将分别考虑这些任务,然后将各公式整合在一起以得到最终结果。
请在此使用 MID 函数。MID可以根据所指定的字符的数量,从所指定的位置开始,从文本字符串中返回特定数量的字符。此函数的语法是:
MID(text,start_num,num_chars)
对于我们的示例,公式为:
=MID(A1,ROW($1:$9),1)
此公式可以分解字母数字字符串,并且实际上会将字符置于工作表的不同行内。例如,对于字母数字字符串abc123
,其所有 6个字符都将被分开。
值得一提的是,字符串分解之后,“1”、“2”和“3”将被看作文本而不是数字。要将存储为文本的数字转换成数字,请用1 乘以此公式,例如:
=1*MID(A1,ROW($1:$9),1)
在此我们将使用 ISNUMBER函数,此函数可以确定字母数字字符串中是否有数字。公式现在变成了:
=ISNUMBER(1*MID(A1,ROW($1:$9),1))
如果字符串中有数字,则结果将为TRUE,否则结果将为 FALSE。
现在我们将通过在上一段中提到的分解后的字符串的结果中查找TRUE 值来确定数字的位置。在此我们将使用 MATCH函数。经过修改的公式现在变为:
=MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0)
如果字符串是 abc123
,则此公式产生的结果将是4,这就是字母数字字符串中第一个数字字符的位置。
现在的任务是计算字符串中数字的数量,以确定返回字母数字字符串中第一个数字之后要返回的字符。
如上所述,可通过用 1与其相乘,将字母数字字符串中存储为文本的数字转换成数字。例如,=1*MID(A1,ROW($1:$9),1)
将存储为文本的数字转换成数字之后,可以通过使用COUNT函数对其进行计数。可通过输入以下公式计算数字的数量:
=COUNT(1*MID(A1,ROW($1:$9),1))
现在我们将使用 MID函数把此公式的各部分整合在一起,如以下示例所示。
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))
从本质上说,此问题可陈述为:确定第一个数字在字母数字字符串(在单元格A1 中)中的位置。返回此数字及其后的数字。
要将得到的字符转换成数字,请用 1乘此公式。虽然对此并不严格要求,但如果要对结果执行数学运算,则应该如此操作。下面是要输入单元格B1 中的最终公式:
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))
要进一步测试此公式,请将下图中的数据输入空工作表中的单元格A1:A7 中。
将此公式输入单元格 B1中,然后使用自动填充将此公式复制到单元格 B2:B7中。(不要忘记按 Ctrl+Shift+Enter。)
在此值得一提的是,如果字符串是 yur09875reew
,而且您使用的是已乘1 的公式,则列 B 中的结果将是 9875 而不是09875。因为 0*1=0,所以 0 被忽略了,返回的结果是9875。如果想得到结果 09875,则不要用 1乘以整个公式。
联系客服