excel从字符串中提取数字——数字位于字符串开头
发布时间:2023-11-21 03:05:38
本文主要研究从字符串开头提取数字的技术:
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一样,字符串中除开头有数字外其它部位存在数字时不影响结果。
猜你喜欢
- 大家知道,要精确调整Excel文档的行高,首先要知道行高值是多少?那怎样知道Excel文档的行高值呢?下面给大家介绍一下。方法一1.把光标移
- 单元格是表格中的最小单位,可拆分。单元格是表格中行与列的交叉部分,它是组成表格的最小单位,单个数据的输入和修改都是在单元格中进行的,那么ex
- 如果用户要将一张Excel表格内容添加到另一张表格中该如何去操作呢?有的朋友会想到复制到另一张表格上这个方法虽然是可以,但是碰到大量的数据复
- 最近有用户反映在打开Word文档后,软件会提示无法打开文件Normal.dotm因为内容有错误,这是怎么回事?Word文档运行时提示无法打开
- 上午在编辑Word文档时,按照文档排版的设计,对其中的文字设置了字体和字号,没有设置以前,文字在Word中显示是正常的,不过,设置了字体字号
- 上周,苹果在经过修复之后给大家推送了iOS16 beta2。虽然已是iOS16的第二个版本,但网友们还是在观望,不敢轻易尝试,大概率是被iO
- 在日常工作中会看到有些同事制作的word文档中的图片上会有文字的存在,那么是如何添加上去的呢?今天小编给大家分享下在word上如何在图片中添
- 苹果今日向 iPhone 用户推送了 iOS 16.1 开发者预览版 Beta 更新(内部版本号:20B5045d),本
- 如果要打印表格中的线条,必须添加边框,可选中表格,点击菜单栏的“格式”——“边框和底纹”,打开“边框和底纹”窗口,并为表格设置边框和底纹。在
- 相信很多小伙伴都在Word文档中设置过限制编辑,这样可以很好地保护我们的文档。但是,如果我们不再需要进行编辑限制,我们就可以停止编辑限制,那
- 有时候为了美观所以要做一些好看的单元格,如果你的表格需要菱形、三角形之类的特殊单元格,那么Excel怎么让单元格形状更具特点?下面小编就为大
- excel电子表格就是最好的计算器,可以进行各种数据运算,那么在excel中怎么进行加减乘除运算?下面小编就为大家介绍excel加减乘除方法
- 打印文档可以算是工作过程中最常用到的技能了。一般来说,打印Word文档时只要点击Word中的“打印”按钮,打印机就会按照Word中的默认设置
- 第1步,打开Word2010文档窗口,切换到“插入”功能区。在“插图”分组中单击“图表”按钮,如图1所示。 图1
- win10局域网无法访问怎么处理呢?很多用户对此还不是很清楚,小编这里就给大家带来有关win10局域网无法访问怎么处理的回答,希望能够对大家
- 在会计行业,要经常输入大写数字,那怎样快速输入大写数字呢?下面给大家介绍一种方法步骤:第一步:在编辑区输入数字123456,并选中第二步:点
- 很多小伙伴可能都有碰到过这个问题,在使用电脑的时候,用着用着键盘就无法输入字符了,按什么键也没有动静。在检查了一下键盘并没有问题,那就只有一
- win10独特的快速启动设置使计算机具有极快的启动速度,但因为我们的电脑上装着各种各样的软件,所以有时启动速度不能过快否则会导致一些应用程序
- 最近有Win10系统用户反映,自己电脑里的账户太多了,想删除掉一下,但又不知该如何删除,为此非常苦恼。那么,Win10系统如何删除账户呢?下
- 我们看到的外国名字中间都会存在一个点,这个点自己在打字的时候却打不出来。那么当我们要打一个外国名字的时候怎么才能打出中间的点呢?不会输入的小