Excel公式技巧:从字符串中提取数字——数字位于字符串开头
发布时间:2022-09-13 22:44:36
Excel公式技巧:从字符串中提取数字——数字位于字符串开头
本文主要研究从字符串开头提取数字的技术:
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怎么创建智能图表NE?很多用户对此还不是很清楚,小编这里就给大家带来有关Excel怎么创建智能图表的回答,希望能够对大家有所帮助。
- 福昕PDF阅读器怎么旋转文件角度与保存?pdf文件想要调整显示的方向,该怎么调整呢?今天我们就来看看使用福昕PDF阅读器设置纵横向显示的教程
- 我们在准备打印输出文档时,为了方便查看都会插入页码,但久而久之你是否发觉每篇文档都使用那几种固定的页码格式,没有丝毫新意,那就让我们一起来精
- 近日,有用户反映,surface Pro 3设备升级到win10系统后,发现网卡找不到,导致无法正常上网。如果遇到这样的问题,怎么解决?对此
- Word作为一款文字编辑工具,在我们的工作生活中有着较为广泛的使用场景,所以掌握一些Word使用技巧,不仅能提高我们的操作效率,还能帮助我们
- 微软Office365试用版怎么免费延长至180天?大家都是到Office365是需要付费使用的,但是有30天的试用期,过了试用期就不能使用
- 实际工作中,对于周期性数据,我们有可能需要制作由触发器驱动的Excel图表,甚至是由多个数据的动态变化来构成一个小型图表应用系统,以此来动态
- 怎么显示Word2013文档中的所有格式?第1步, 打开Word2013文档窗口,依次单击“文件”→“选项”按钮。Word2013第2步,在
- Excel怎么制作漂亮的日历?excel表格中想要制作一个日历,该怎么制作日历呢?下面我们就来看看Excel表格制作日历的教程,需要的朋友可
- 有时候我们需要在EXCEL里填充大量的数据,比如说有规律的序列,一个一个填充真的是要累死人的节奏有木有,下面小编就为大家介绍Excel利用填
- 许多用户在使用电脑的时候,都会进行一些个性化的设置。而在这些个性化设置中,任务栏肯定躲不过用户的更改,有些用户在使用的时候都会将任务栏设置成
- 操作方法1、选择“审阅”—“翻译”—“翻译屏幕提示”,即开启翻译屏幕提示功能。 2、将鼠标停留在单词上,系统会自动
- 学习Excel函数,就一定会接触VLOOKUP函数,正常都是从左向右查询,偏偏遇到就是需要从右往左查询,这样反向查询应该怎么做呢?学习本文教
- 如果您发送一个文档以供多名审阅者审阅,并且每名审阅者都返回文档,则可以按照一次合并两个文档的方式组合这些文档,直到将所有审阅者修订都合并到单
- 本期Word小编与大家分享几个在工作中经常会遇到的Word表格问题,一起来看看吧。1、如何禁止表格列宽随输入的文字而变化?在Word表格中输
- Excel 2010怎样将公历转为农历的方法给大家介绍一下这几乎是个大难题了,好像只能用代码来解决的,其实如果用Excel2010版就不需要
- 相信很多小伙伴们在Word文档中编辑和处理文本内容时,都会插入一些相关图片,我们现在基本上很少看到纯文本内容的Word文档了。插入图之后,我
- 用户在使用Word2010编辑文档的过程中,常常需要将Word文档中的特定文字设置为隐藏文字,以实现保密效果。那么在word2010文档中怎
- 今天小编为大家分享用Word自己创建与使用公司/企业/学校模板,对于想要自己设计模板的朋友可以参考本文,希望这篇文章能对大家有所帮助我们可以
- word表格清除内容的方法1、打开电脑找到并双击打开word2018文档软件;2、点击打开word文档软件以后,此时为了后期的示范,事先编辑