hercules028 / excel / Excel公式技巧10: 从字符串中提取数字—...

0 0

   

Excel公式技巧10: 从字符串中提取数字——数字位于字符串开头

2020-02-23  hercules0...

本文主要研究从字符串开头提取数字的技术:

1. 这些数字是连续的

2. 这些连续的数字位于字符串的开头

3. 想要的结果是将这些连续的数字返回到单个单元格

对于下面研究的每种解决方案,我们需要在两种不同的情况下测试其健全性:

1. 字符串中除开头外其他地方没有数字的情况,例如123ABC。

2. 字符串中除开头外其他地方也有数字,要么在末尾,要么在中间,例如123ABC456或123ABC456DEF。

无论字符串中除开头外是否还有其他数字,将要研究的某些解决方案都可以很好地工作,但有些解决方案则存在局限性。在分析每种解决方案时,将会明确说明。

LOOKUP与LEFT

公式1:

=-LOOKUP(1,-LEFT(A1,ROW(INDIRECT('1:'& LEN(A1)))))

如果单元格A1中的内容为“123ABC”,那么上述公式1返回“123”。其解析过程如下:

ROW(INDIRECT('1:' & LEN(A1)))

生成一个由1至单元格A1中字符串长度数的整数组成的单列数组:

{1;2;3;4;5;6}

这样,公式1变为:

=-LOOKUP(1,-LEFT(A1,{1;2;3;4;5;6}))

由于LOOKUP强制生成数组,因此LEFT在这里不是返回单个值,而是返回由六个值组成的数组,每个值对应于将LEFT的num_chars参数指定为1、2、3、4、5、6应用于A1中的字符串,即:

=-LOOKUP(1,-{'1';'12';'123';'123A';'123AB';'123ABC'})

其中的数组乘以-1,得到:

=-LOOKUP(1,{-1;-12;-123;#VALUE!; #VALUE!; #VALUE!})

对于LOOKUP来说,如果在lookup_vector中未找到lookup_value,并且假设lookup_vector中没有大于lookup_value的值,则该函数将从lookup_vector中返回最后一个值(本例中为数字)。该函数还会忽略lookup_vector中的任何错误值。这就是在开始给lookup_vector(通过创建一个由负数、零(如果期望提取的字符串以0开头例如0123ABC)或错误值组成的数组)中的值添加负号的原因,可以确保lookup_value为1永远是一个充分而合法的选择。在这里,由于在lookup_vector中找不到1,公式返回数组中最后一个数值,即-123。

当然,这绝对不是处理这种公式结构的唯一方法,只要确保选择的lookup_value的值足够大。其实这并不困难,让lookup_value使用所谓的“大数”(即9.99999999999999E+307,这是Excel中允许的最大正数),确保这种公式构造有效。或者,有些人喜欢仅取“非常大”的值,例如10^10(其好处是看起来不像“大数”那么笨拙)。

由于公式1中LOOKUP函数返回-123,因此在前面添加一个负号使其变为想要的123。

但是,公式1并不可靠。因为某些字符串可能会返回其他结果,例如单元格中的值为12JUN,那么:

=-LOOKUP(1,-LEFT(A1,ROW(INDIRECT('1:'& LEN(A1)))))

转换为:

=-LOOKUP(1,-LEFT(A1,{1;2;3;4;5}))

转换为:

=-LOOKUP(0,-{'1';'12';'12J';'12JU';'12JUN'})

此时,会转换为:

=-LOOKUP(0,{-1;-12;#VALUE!;#VALUE!;-43994})

这是由于在强制将“12JUN”转换成数字时,Excel认为其是日期“2020-6-12”,因此将其转换为相应的序列数字。此时,LOOKUP函数返回-43994。

当然,这不是唯一会出现这种情况的字符串,实际上,任何可以被Excel解释为日期的字母数字都会如此,例如30SEP、01FEB等,这也会导致不正确的结果。

此外,公式1对于诸如123E3等形式的字符串也无效,其结果将是123000。因为在通常情况下,将123E3输入单元格后,Excel会自动将其转换成科学计数格式。

LEFT与COUNT

公式2:

=0+LEFT(A1,COUNT(0+MID(A1,ROW(INDIRECT('1:'&LEN(A1))),1)))

这是一个数组公式。

仍以单元格A1中的数据是“123ABC”为例。公式2可以转换为:

=0+LEFT(A1,COUNT(0+MID(A1,{1;2;3;4;5;6},1)))

转换为:

=0+LEFT(A1,COUNT(0+{'1';'2';'3';'A';'B';'C'}))

转换为:

=0+LEFT(A1,COUNT({1;2;3;#VALUE!;#VALUE!;#VALUE!}))

COUNT函数忽略错误值,得到:

=0+LEFT(A1,3)

结果为:

123

下面,尝试一个公式2可不可以处理除字符串开头外其他位置还存在数字的情形,例如如果单元格A1中的数据是“123ABC45”,那么公式2可以转换为:

=0+LEFT(A1,COUNT(0+MID(A1,{1;2;3;4;5;6;7;8},1)))

转换为:

=0+LEFT(A1,COUNT(0+{'1';'2';'3';'A';'B';'C';'4';'5'}))

转换为:

=0+LEFT(A1,COUNT({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}))

转换为:

=0+LEFT(A1,5)

转换为:

=0+'123AB'

结果为:

#VALUE!

原因是字符串的末尾有其他数字,因此COUNT函数统计的数字个数大于字符串开头的数字个数,这样LEFT取值仍是字母数字混合的字符串。

LEFT、MATCH与ISNUMBER

公式3:

=0+LEFT(A1,MATCH(FALSE,ISNUMBER(0+MID(A1,ROW(INDIRECT('1:'& LEN(A1))),1)),0)-1)

这是一个数组公式。

仍以单元格A1中的数据为“123ABC”,公式3可转换为:

=0+LEFT(A1,MATCH(FALSE,ISNUMBER({1;2;3;#VALUE!;#VALUE!;#VALUE!}),0)-1)

对于ISNUMBER函数来说,传递给它的如果是错误值则返回FALSE,因此上述公式可转换为:

=0+LEFT(A1,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

转换为:

=0+LEFT(A1,4-1)

转换为:

=0+LEFT(A1,3)

结果为:

123

在公式3中,MATCH/ISNUMBER组合确保字符串中除开头以外的数字不会影响最终的结果。例如如果单元格A1中的数据是“123ABC45”,那么公式3可以转换为:

=0+LEFT(A1,MATCH(FALSE,ISNUMBER({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}),0)-1)

转换为:

=0+LEFT(A1,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE},0)-1)

转换为:

=0+LEFT(A1,4-1)

转换为:

=0+LEFT(A1,3)

结果为:

123

LEFT、MATCH与ISERR

与公式3的构造一致,唯一的不同是使用ISERR函数代替了ISNUMBER函数,并强制返回由数字组成的数组。

公式4:

=0+LEFT(A1,MATCH(1,0+ISERR(0+MID(A1,ROW(INDIRECT('1:'& LEN(A1))),1)),0)-1)

这是一个数组公式。

仍以单元格A1中的数据为“123ABC”,公式4可转换为:

=0+LEFT(A1,MATCH(1,0+ISERR({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}),0)-1)

转换为:

=0+LEFT(A1,MATCH(1,0+{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE},0)-1)

转换为:

=0+LEFT(A1,MATCH(1,{0;0;0;1;1;1;0;0},0)-1)

转换为:

=0+LEFT(A1,4-1)

转换为:

=0+LEFT(A1,3)

结果为:

123

与公式3一样,字符串中除开头有数字外其它部位存在数字时不影响结果。

注:本技巧整理自excelxor.com

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    猜你喜欢

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多