Excel 公式:从文本中提取数字

大家好啊,今天和大家分享 3 个从文本中提取数字的公式。

首先看一下实例图片,在一串文本中,数字有可能在文本的开始处、结尾处或中间。

现在针对这三种情况,给出三种提取数字公式。

数字在文本开始处

这种情况提取数字相对简单些,以下是通用公式:

=-LOOKUP(0,-LEFT(要提取数字的单元格,ROW($1:$15)))

公式中,ROW($1:$15)部分数字 15 表示想要提取的数字的最长长度,可以根据实际需求调整。

本例中,实际的公式如下:

=-LOOKUP(0,-LEFT(B3,ROW($1:$15)))

 

 

数字在文本结尾处

类似上一公式,这种情况的通用公式:

=-LOOKUP(0,-RIGHT(要提取数字的单元格,ROW($1:$15)))

公式中,ROW($1:$15)部分数字 15 表示想要提取的数字的最长长度,可以根据实际需求调整。

本例中,实际的公式如下:

=-LOOKUP(0,-RIGHT(B3,ROW($1:$15)))

 

 

数字在文本中间

这种情况相对复杂些,但是思路是一致的,以下是通用公式:

=-LOOKUP(1,-RIGHT(LEFT(要提取数字的单元格,LOOKUP(10,--MID(要提取数字的单元格,ROW($1:$20),1),ROW($1:$18))),ROW($1:$20)))

公式中,ROW($1:$20)部分数字 20 表示想要提取的数字的单元格长度,可以根据实际需求调整。

本例中,实际的公式如下:

=-LOOKUP(1,-RIGHT(LEFT(B3,LOOKUP(10,--MID(B3,ROW($1:$20),1),ROW($1:$20))),ROW($1:$20)))

 

 

通用公式

大家可能已经思考了,第三个公式适用于前两种情况吗?答案是“肯定适用!”。开始和结尾都是特殊的中间情况。

但是如果明确数字在开始处或结尾处,使用对应的公式会更简洁一些。

有问题欢迎在留言处交流。

 

转载注明:文章转载自「懒人Excel - www.lanrenexcel.com

您可能还喜欢...

订阅评论
提醒
106 评论
最新
最旧 最多投票
内联反馈
查看所有评论
饿也不吃糖
2 月 前

lookup的用法有介绍吗?


快乐向前
3 月 前

为啥在vlookup前面有个减号

阿斯顿发发
4 月 前

如果是有两个呢 asdfasd12342sadf33334sdf,这种

峰回路转
5 月 前

可以出一版函数使用教程吗?就是每一个函数使用的原理,比方说:SUM 它是数字相加的总和,选中需要相加数字的行或者列,就会得出总和

PP踢
9 月 前

是不是能用excel的智能填充

会飞的猪
9 月 前

Excel是 VBA其中的一种表达方式吧 是vba好学还是Phoshop好学

COCO
11 月 前

第3个公式咋理解,没看懂

data
11 月 前

写的太棒了 加油

fyj
1 年 前

为啥我原封不动粘贴公式,出来是错误,显示这个#N/A

zmschina
1 年 前

一步出不来就分步做呗,先分列,一个字符一列,再if(isnumber)判断,不是数字就为空,最后textjoin组合起来。或者按着上边的步骤在vba里自定义公式

mhm80000
1 年 前

在一串文本DN300-1.0中,结尾处数字为1.0时,提取的数值为-1而不是1,如何调整公式?

学习小白
1 年 前

假如数据含有的文字数量是相同的,且是在左边时,我们可以使用提取函数right函数,截取对应的数字个数就行了。以上是自己的解法,如有不对,请大家指教指教

countblue
1 年 前

a1b2c3这种情况不适用于这个公式 有什么办法么 谢谢

lfyhuhu
10 月 前
回复给  countblue

=TEXTJOIN(“”, TRUE, IF(ISNUMBER(VALUE(MID(A1, ROW(INDIRECT(“1:” & LEN(A1))), 1))), MID(A1, ROW(INDIRECT(“1:” & LEN(A1))), 1), “”))

mll'
1 年 前

怎末在表格里面插入动态图片?哪位大神会。我找遍全网没找到一个有用的

凌云志
11 月 前
回复给  懒人Excel

直接插GIF,难道不行吗?

heyren
1 年 前

为啥公式前需要加负号哦

大力水手
1 年 前
回复给  懒人Excel

负号不用行吗?

零零零
1 年 前

excel2019新增的函数不更新一下吗?站长好人一生平安。

MMZ
1 年 前

=-LOOKUP(0,-LEFT(A19,ROW($1:$15)))
输入ROW($1:$15)后就变成引用1-15行了,请问这是什么原因呢?

初学VBA
11 月 前
回复给  懒人Excel

6

qlqq
1 年 前

好像15位以上的数字提出来后面就变成多个00,与原数字不一样 这要怎么解决呢

贰當家
1 月 前
回复给  懒人Excel

请原谅我的无知。。

贰當家
1 月 前
回复给  懒人Excel

原来15是这么来的啊!!纳闷了半天。。

cllll
1 年 前

太好用了

随缘随缘
1 年 前

太实用了

红红火火
2 年 前

LOOKUP第一个值是啥含义?一会是0 一会1 那么0适合什么场景?1适合什么场景?

大猫哥哥
2 年 前

有没有讲解呀,操作都很简单,就是不懂原理

阿然*
2 年 前

爸爸 能有视频教程最好了

麦呦儿
2 年 前

选定数字crtl+c然后crtl+e即可

1111
1 年 前
回复给  麦呦儿

好像不可以,要引用示例,就不知道该怎么操作了

windcliff
1 年 前
回复给  1111

在你所需要提取的数列旁边使用就行,需要注意的是,原数列中间不要有间断,即中间不能空行,我的做法是用 – 代替空行,这样后面提取出数字后也可以很方便的替换掉 –

卷发好男人
2 年 前

很好很强大

孩儿他爹
2 年 前

尾数是双数0的话取值就会是0,比如:abc100 ,abc10000

2 年 前

Great content! Keep up the good work!

努力努力再努力
2 年 前

能把数字提取出来还能把原单元格里面的数字删除吗

DELU
2 年 前

还有,表格里两个数字的时候为啥不可以,也显示#N/A

xiaomi
2 年 前
回复给  DELU

你是不是写的是ABC12,然后用=-LOOKUP(0,-LEFT(F50,ROW($1:$15)))它寻找,#N/A这个意思是无法找到数值,数字在右边要用right。

DELU
2 年 前

=-LOOKUP(1,-RIGHT(LEFT(E2,LOOOKUP(10,–MID(E2,ROW($2:$15),1),ROW($2:$15))),ROW($2:$15)))
老师,这哪里的问题,单元格中出现了#N/A

DWsazn
2 年 前

还是有点不明白这句话都(部分数字 15 表示想要提取的数字的最长长度,可以根据实际需求调整)
我输入了15然后格子全部变成粉红色了,我以为说15就是粉红色格子15个,然后去数了不对的,全部变粉红色了

xiaomi
2 年 前
回复给  懒人Excel

=-LOOKUP(0,LEFT(K3,ROW($1:$3)))

DWsazn
2 年 前

我已经爱上你了,怎么会有这种东西,偶吼吼

Rick
2 年 前

公式前面加个负号是什么意思

韩bb
2 年 前

这个方法已经get
谢谢站长

韩bb
2 年 前

谢谢站长分享

ali
2 年 前

坚持下去啊站长

ItEmy吖
2 年 前

数字在文本中间这个公式一直用不了吖

Po、
2 年 前

建议对操作方式排个序

柒与騹
2 年 前

=-LOOKUP(0,-RIGHT(B2,ROW($1:$12))),老师看下哪里不对

宝贝精灵_66
2 年 前

学习了,对工作很有帮助,谢谢老师分享

Jaffen
2 年 前

如果有多组数字夹杂文本,比如数字跟文本再跟数字再跟文本,上面的那个通用公式提出的结果就总是最后一组数字。

兔小白
2 年 前

思路是利用函数返回数组,再作为实参向下级函数传递是吧?

楠楠*
3 年 前

这公式好长啊

53ddd
3 年 前

用Ctrl+E更快

wq123
3 年 前

谢谢分享,然后个人关于上述公式存在一些疑问,还有关于数字穿插在文本中提取的个人见解方法已在懒人Excel公众号留言,期待您的回复。

hopper
3 年 前

-LOOKUP(0,-LEFT(B3,ROW($1:$15))),请问函数前为什么要加负号啊,不加的话确实函数就计算错误了

HawnHan
3 年 前
回复给  hopper

对公式逐步进行分解你就明白了。首先left,right,mid这三个公式取出来的值都是文本,在这之前+负号相当于文本×-1,那不是纯数字的内容会变成错误值,lookup在查找不到准确对应值时会返回小于第一参数的内容中的最小值,最后再用负号将其转为正数

林恩223
3 年 前

你好,我对于Excel也有一些总结,特别是数据处理这一块,我可以分享下这些知识,但是平台有提供这个入口吗

提乐乎
3 年 前

lookup函数找数值0,{-1;-12;-123}中小于或等于0的最大值不是-1么?

提乐乎
3 年 前
回复给  懒人Excel

厉害,明白了,谢谢指导

路人甲
3 年 前

great!

我是松子呀
3 年 前

文中的取中间的数字那里,公式里面的“1”是什么意思啊?

106
0
希望看到您的想法,请您发表评论x