友情提示:本文共有 1428 个字,阅读大概需要 3 分钟。
这几天在百度知道里回答关于EXCEL的问题。面对各种各样的问题,让我感觉我所了解的EXCEL知识就算是解决常用基本问题,也是不能妥善解答的。比如前天回答了一个关于AVERAGE()函数的问题,求平均数函数,常用吧?问题是问AVERAGE(13,TRUE,-5)等于多少?什么?还有逻辑值?赶紧查AVERAGE在包含逻辑值时是否计算……
这只是一个小插曲,我在回答的问题中有很多是字符提取或数字提取的问题,比如:如果提取数量单位混合填写中的数量值,或是如何提起车牌号的最后一位数字?大家了解么?今天我们就来聊一聊这个问题。
如何提取数量单位混合填写中的数量值:
如下图,如何提取数量/单位中的数量值?如果是数量值或是单位位数统一,比如都是元、万元等,直接数值-分列即可。但下图中恰恰是数量和单位位数都不统一,那如何取值?
我想的用常用的公式组合可以用两种方法解决:
一、SUBSTITUTE函数+字符串提取函数,先看公式C13=SUBSTITUTE(B13,RIGHT(B13,LENB(B13)-LEN(B13)),"")
分解一下公式:
1、LENB(B13)-LEN(B13)=1,利用LENB()和LEN()函数计算字符长度不同作相减,得到即文本中的汉字个数(关于LENB和LEN函数的介绍,参见关于文本函数的发文);
2、RIGHT(B13,LENB(B13)-LEN(B13)),即RIGHT(B13,1)=“元”;
3、SUBSTITUTE(B13,RIGHT(B13,LENB(B13)-LEN(B13)),""),即SUBSTITUTE(B13,"元","")=3000,SUBSTITUTE()函数是常用替换函数之一,结构是SUBSTITUTE(文本内容,需要替换的文本,替换为),所以该函数即从"3000元"中,将“元”替换为空,从而得到文本中的数字部分。
过程基本如上,注意该函数组合因为是文本替换函数为结尾,最后得到的数值是文本型。所以截图中3000是靠左的。
二、LOOKUP函数+字符串提取函数+ROW(),第一种提取虽然可以完成,但我不作重点推荐,个人偏好使用EXCEL中的查找神器LOOKUP函数,这个是可以成套路的。
先看公式C12=-LOOKUP(1,-LEFT(B12,ROW($2:$100))),接下来分解一下:
1、ROW($2:$100)=(1,2,……100),ROW()行函数,没啥可说的。为啥是100?只是为了足够大,其实超过被查文本字符数即可;
2、-LEFT(B12,ROW($2:$100)),=-LEFT(B12,(1,2……100)),即将B12从左边开始按照1、2……数量取字符,结果为-LEFT("1","18","18.","18.9","18.9万","18.9万元",……"18.9万元"),LEFT()添加负号后,纯数值文本转为负数,含有文本字符的文本转换为"#value!";
3、-LOOKUP(1,-LEFT(B12,ROW($2:$100))),第一参数是1,所以在有LEFT生成的数组中忽略错误值,提取最后一个数值,最后外围再加一个符号,转换为正值,即题目所需。
该函数取得的结果是数值型,可以直接加和等处理。
好了,今天的混合文本提取数值的内容就介绍到这里,最后给大家留已到文章开头提到的题目,大家可以用LOOKUP+字符串提取+ROW()套路函数思考一下:下图中如何提取车牌尾号?答案下期发文公布。
晚安
本文如果对你有帮助,请点赞收藏《EXCEL中混合文本中如何提取数字部分?》,同时在此感谢原作者。