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

Excel公式技巧：从字符串中提取数字。——数字在字符串的开头。

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

1.这些数字是连续的。

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

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

对于下面研究的每个解决方案，我们需要在两种不同的情况下测试其可靠性：

1.除了开头，字符串中没有数字，例如123公元前。

2.字符串中除开头之外都有数字，要么在末尾，要么在中间，如123ABC456或123ABC456DEF。

不管字符串中除了开头之外是否还有其他数字，一些要研究的解都会很好用，但是有些解是有局限性的。在分析每种解决方案时，都会明确说明。

查找并向左

公式1:

=-LOOKUP(1，-LEFT(A1，ROW(INDIRECT("1:" LEN(A1)))))

如果单元格A1中的内容为“123ABC”，则上述公式1返回“123”。解决过程如下：

行(间接(“1:”透镜(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，即：

=-LOOKUP(1，-{“1”；)12;"123; "123 a”；123AB”公元前123年" })

将数组乘以-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.9999999999e307，这是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解释为日期的字母数字字符上，如09月30日、01月2日等。这也会导致不正确的结果。

另外，公式1对于123E3等字符串也是无效的，结果会是123000。正常情况下，在单元格中输入123E3后，Excel会自动转换为科学计数格式。

向左数

等式2:

=0 LEFT(A1，COUNT(0 MID(A1，ROW(间接(“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; "一个“；”b "；"C"}))

转换为：

=0 LEFT(A1，COUNT({ 1；2;3;#VALUE！#VALUE！#VALUE！}))

COUNT函数忽略错误值，并获取：

=0左(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; "一个“；”b "；"c "；"4;"5}))

转换为：

=0 LEFT(A1，COUNT({ 1；2;3;#VALUE！#VALUE！#VALUE！4;5}))

转换为：

=0左(A1，5)

转换为：

=0 "123AB "

结果是：

#VALUE！

原因是字符串末尾还有其他数字，所以count函数COUNTed的数字个数大于字符串开头的数字个数，所以LEFT值仍然是字母和数字混合的字符串。

左，匹配和是数字。

等式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，is number({ 1；2;3;#VALUE！#VALUE！#VALUE！}),0)-1)

如果是错误值，ISNUMBER函数返回FALSE，因此上述公式可以转换为：

=0 LEFT(A1，MATCH(FALSE，{ TRUE真；真；假；假；FALSE}，0)-1)

转换为：

=0左(A1，4-1)

转换为：

=0左(A1，3)

结果是：

123

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

=0 LEFT(A1，MATCH(FALSE，is number({ 1；2;3;#VALUE！#VALUE！#VALUE！4;5}),0)-1)

转换为：

=0 LEFT(A1，MATCH(FALSE，{ TRUE真；真；假；假；假；真；真}，0)-1)

转换为：

=0左(A1，4-1)

转换为：

=0左(A1，3)

结果是：

123

左，匹配和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}，0)-1)

转换为：

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

转换为：

=0左(A1，4-1)

转换为：

=0左(A1，3)

结果是：

123

和公式3一样，除了开头的数字之外，字符串其他部分中数字的存在不会影响结果。