从文本中截取内容通常都会用到Left、Right、Mid、Find这几个函数。这几个函数本身并不难,参数也比较少,很容易理解。但是用他们组合起来,或者加上其他函数组合起来截取指定的内容,那就不一定简单了。今天我们从基础开始到略深入,讲一下截取的几个套路。
基础
Left的英文意思是左边,作为Excel函数就代表从左边开始截取字符。
Right的英文意思是右边,作为Excel函数就代表从右边开始截取字符。
Mid的英文意思是中间,作为Excel函数就代表从中间开始截取字符,当然也可以从第一个字符开始截取。
Find的英文意思是查找,作为Excel函数就代表查找一个字符串在另一个字符串中的位置,并且可以指定从第几个字符开始查找,注意这里指的是从第几个字符开始搜索,返回的仍然是它本身所在的位置。
这几个函数的语法都比较简单,从字面意思也很容易判断如何使用。
语法
LEFT(text, [num_chars])
RIGHT(text,[num_chars])
MID(text, start_num, num_chars)
FIND(find_text, within_text, [start_num])
这几个函数都有一个按字节处理的函数就是在函数结尾加上B(B代表Byte),在计算机中,一个汉字、全角符号等代表两个字节,所以大家需要注意区分字符个数、字节个数。
这些函数跟上面的函数具有相似的语法,如下:
LEFTB(text, [num_bytes])
RIGHTB(text,[num_bytes])
MIDB(text, start_num, num_bytes)
FINDB(find_text, within_text, [start_num])
大家注意看Left、Right、Find、LeftB、RightB、FindB函数的最后一个参数都是可选的,如果不填的话就默认为1。
简单示例
通过以下几个示例,我们可以先简单了解一下这几个函数。
这几个函数看起来简单,但是组合起来也可以很复杂,运用得当的话也能解决很多实际问题。
经常见到前面加负号或两个负号?
这是因为Left、Right、Mid函数返回的结果是文本,如果你要截取数字并得到真正的数值,就得用两个负号将它变成真正的数值。当然了,我们也可以用其他方式来转换,比如乘以1。
情景一:数字在左边起始位置
思路:
1、从左边开始截取字符,分别截取1个、2个、3个字符……
2、然后将截取的值转换成数值查找最大的值。
公式1:
=-LOOKUP(,-LEFT(A2,ROW($1:$99)))
公式2:这是数组公式,需要按Ctrl Shift Enter
=MAX(IFERROR(--LEFT(A3,ROW($1:$100)),0))
解释:
公式中ROW($1:$99)是为了简便起见,现实中一般也不会有这么大的数。Right加上这个数组参数就表示从左边开始分别截取1~99个字符。
Left前面的负号表示将Left截取的结果转换成负数,如果是文本,那么就转换成错误值。
Lookup第一个参数省略了,其实第一个参数就是个0,第二个参数除错误值外都是负数,这样就找到了最后一个负数,也就代表了长度最大的一个有效数值。
最后Lookup外面再用一个负号就将转换成正值了。
第2个公式中的IfError是为了屏蔽将文本转换成数值造成的错误值。
关于Lookup的用法,请参考以下文章。
情景二:数字在右边结束位置
公式跟上面的比较相似,只不过是改成用Right从右边开始截取。
公式1:
=-LOOKUP(,-RIGHT(A9,ROW($1:$99)))
公式2:这是数组公式。
=MAX(IFERROR(--RIGHT(A10,ROW($1:$100)),0))
情景三:数字在不确定的位置
这时就需要用到Mid了,因为Mid可以从指定的位置开始截取字符。
思路:
1、先找到第一个数字出现的位置,然后从这个位置开始截取字符,截取1个、2个、3个……
2、将截取的内容转换成数值并求最大的那个数。
公式1:数组公式
=-LOOKUP(,-MID(A15,MATCH(,0/MID(A15,ROW($1:$99),1),),ROW($1:$99)))
说明:
ROW($1:$99)是简略的写法,如果字符数超过99个,则可以将99改为更大的数,或者用ROW(INDIRECT('1:'&LEN(A15)))来代替(见公式2)。
0/MID(A15,ROW($1:$99),1),如果是数字,则结果是0,否则结果是错误值。
MATCH(,0/MID(A15,ROW($1:$99),1),)查找第一个数字出现的位置。
MID(A15,MATCH(,0/MID(A15,ROW($1:$99),1),),ROW($1:$99))从第一个数字的位置开始截取,分别截取1~99个字符,然后用负号转换成负值,如果是文本则变成错误值。
最后用Lookup查找最后一个负数数值,最外面用一个负号转换成正数。
公式2:数组公式
=-LOOKUP(,-MID(A16,MATCH(,0/MID(A16,ROW(INDIRECT('1:'&LEN(A16))),1),),ROW($1:$99)))
公式3:数组公式
=MAX(IFERROR(--MID(A17,MATCH(,0/MID(A17,ROW($1:$99),1),),ROW($1:$99)),0))
说明:这个Max的公式跟上面的情境中使用原理一致。
公式4:数组公式
=-LOOKUP(,-MID(A17,MIN(FIND(ROW($1:$10)-1,A17&1/17)),ROW($1:$99)))
说明:这里用Find查找数字出现的位置,注意其中1/17这种用法,相信很多同学都不明白,使用1/17是因为1/17的结果中包含0~9所有数字,是为了Find的容错处理,可以简化公式,当然可以采用直接写数字这种方法'1234567890'(见公式5),也可以用IfError来容错(见公式6)。
公式5:数组公式
=-LOOKUP(,-MID(A18,MIN(FIND(ROW($1:$10)-1,A18&'1234567890')),ROW($1:$99)))
公式6:数组公式
=-LOOKUP(,-MID(A19,MIN(IFERROR(FIND(ROW($1:$10)-1,A19),9E 307)),ROW($1:$99)))
总之各种公式都是用相应的套路,学会之后灵活组合就可以了。
情景四:数字有前导0存在
在情景3的基础上又增加了一点,数字中有前导0存在,需要提取全部的数字。
公式:数组公式
=MID(A26,MIN(FIND(ROW($1:$10)-1,A26&1/17)),MAX(IF(ISNUMBER(--MID(A26,ROW(INDIRECT('1:'&LEN(A26))),1)),ROW(INDIRECT('1:'&LEN(A26))),0))-MIN(IFERROR(FIND(ROW($1:$10)-1,A26),E9 307)) 1)
说明:
这里有前导0存在就不能使用处理数值那样的方法了。
求最后一个数字出现的位置的公式MAX(IF(ISNUMBER(--MID(A26,ROW(INDIRECT('1:'&LEN(A26))),1)),ROW(INDIRECT('1:'&LEN(A26))),0)),看起来很复杂,其实原理就是每次截取一个字符,负负号转换后看看是不是数字,如果是的话就记录位置,否则的话就记录0,看最大的位置是哪个。
情景五:金额填充到每个单元格
如下图所示,将金额填充到后面的每个单元格中。
在C1中输入以下公式,然后向右拉,向下拉。
=LEFT(RIGHT(REPT(' ',11)&'¥'&$A2*100,12-COLUMN(A:A)))
解释说明:
从“亿”到“分”总共11个单元格,先把数字乘以100是为了获取小数位,并且可以去掉包含小数点的麻烦。
REPT(' ',11)是为了填充空格,当数字位数不够时可以截取到空格。
12-COLUMN(A:A),当在C列时,表示截取11个字符,当在M列时表示截取1个字符。
先用Right截取相应长度的字符,再用Left截取最左边的字符。
身份证中1~6位数表示地区信息,7~14位表示出生年月日(YYYYMMDD格式),15~17位表示同一地区的同年同月同日出生的人的顺序号,奇数表示男,偶数表示女。最后一位数字是校验码。
所以我们可以在C2单元格输入以下公式来获取出生日期。
=TEXT(--MID(B2,7,8),'0-00-00')
注意:
这个结果是文本格式,如果需要转换成真正的日期,只需要在Text前面加上两个负号即可,如下。
=--TEXT(--MID(B2,7,8),'0-00-00')
在D2单元格输入以下公式。
=IF(MOD(MID(B2,15,3),2),'男','女')
说明:
Mod是求余数的函数,MOD(MID(B2,15,3),2)结果是1表示是奇数,代表男性,结果是0时表示是偶数,代表女性。
在公式中数字0代表false,除此之外的其他数字都代表True。
小伙伴们,这些套路你都学会了吗?
暂时没搞明白也没关系,收藏起来,说不定哪天就用到了呢!
--End--
联系客服