Excel 文本字符提取函数三剑客LEFT、MID、RIGHT经典应用详解
本文于2023年7月16日首发于本人同名公众号:Excel活学活用,更多文章案例请搜索关注!
内容提要
字符截取(提取)函数
大家好,我是冷水泡茶,Excel文本、字符处理函数在我们前面的推文中多有提及,今天我们集中了解一下以下三个常用函数:
1、LEFT:从文本左边截取一段字符;
2、MID:从文本中间截取一段字符;
3、RIGHT:从文本右边截取一段字符。
LEFT函数
定义:从文本字符串的第一个字符开始返回指定个数的字符。
语法:LEFT(text, [num_chars])
text 必需。 包含需要截取字符的文本。
num_chars 可选。需要截取的字符的长度,必须大于等于0,如果大于整个text的长度,则返回text的全部文本,若省略,则截取1个字符。
经典应用场景:处理会计科目
科目代码 | 科目名称 | 一级代码 | 一级名称 |
100101 | 库存现金\人民币 | =LEFT(A2,4) | =LEFT(B2,FIND("\",B2)-1) |
说明:假设我们有一批明细科目,需要取得它的一级代码与一级名称,输入如上表所示公式,即可得到我们想要的结果:
一级代码:科目代码一般遵循一定的编码规则,一级代码4位,其余2位一级。那么,我们直接从“科目代码“左起截取4位即可。
一级名称:这个有点不同,一级科目名称的长度是不统一的,我们得另想他法。
我们可以利用科目的“分级符”,这里是“\”(有的可能是“-”,甚至“/”,都没有关系,统一就好)。
我们利用FIND函数取得字符“\”的位置,再减去1,就是其前面一级科目名称的长度。再利用LEFT函数提取即可。
RIGHT函数
定义:根据所指定的字符数返回文本字符串中最后一个或多个字符。
语法:RIGHT(text, [num_chars])
text 必需。 包含需要截取字符的文本。
num_chars 可选。需要截取的字符的长度,必须大于等于0,如果大于整个text的长度,则返回text的全部文本,若省略,则截取1个字符。
经典应用场景:处理会计科目
科目代码 | 科目名称 | 末级科目 |
100101 | 库存现金\人民币 | =RIGHT(B2,LEN(B2)-FIND("\",B2)) |
100101010101 | 库存现金\人民币1\人民币2\人民币3\人民币4 | =RIGHT(B3, LEN(B3)-FIND("@",SUBSTITUTE(B3,"\", "@",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))) |
说明:假设我们有一批明细科目,需要取得它的末级科目,输入如上表所示公式,即可得到我们想要的结果:
1、第一条科目,只有2级,我们简单处理,如果科目级别不统一,那么这个公式就不行了。
2、第二条科目,有点夸张,现实中很少有这么多级的吧。就是为了说明科目的级别各不相同,我们用1的处理方法就不行了。也得另寻他法:
=RIGHT(B3, LEN(B3)-
FIND("@",SUBSTITUTE(B3,"\", "@",LEN(B3)-
LEN(SUBSTITUTE(B3,"\","")))))
这个公式有点长,重点就在于如何找到最后一个“\”的位置?
(1)我们用SUBSTITUTE函数,先把“\”替换成空“”,然后用原始文本的长度减去替换后文本的长度,得到字符“\”的数量m,这里是4。
(2)由于我们是要找出最后一个“\”的位置,我们再用SUBSTITUTE函数,把第m个“\”替换成另外一个独特的字符(在原文本中没有的),这里用的是“@”,那么原来的文本就变成了“库存现金\人民币1\人民币2\人民币3@人民币4“。
(3)再用FIND函数,找到字符“@”的位置n。
(4)再用原来文本的长度减去n,即得到末级科目的长度。
(5)最后,用RIGHT函数提取出末级科目。
MID函数
定义:返回文本字符串中从指定位置开始的指定数目的字符。
语法:MID(text, start_num, num_chars)
text 必需。 包含需要截取字符的文本。
start_num 必需。文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1,以此类推。
num_chars 必需。指定需要从文本中提取字符的个数。
经典应用场景1:处理会计科目
科目代码 | 科目名称 | 3 |
100101010101 | 库存现金\ | =SUBSTITUTE(SUBSTITUTE(B2,"\","@",$C$1-1),"\","|",$C$1-1) |
说明:假设我们有一批明细科目,需要取得它的中间级科目,输入如上表所示公式,即可得到我们想要的结果(这里根据表头数字3来编写公式,表示第3级。根据上例中科目名称的级数,它在2~4范围内变化,均能取得正确的中间级别的会计科目):
=MID(SUBSTITUTE(SUBSTITUTE(B2,"\","@",$C$1-1),"\","|",$C$1-1),
FIND("@",SUBSTITUTE(SUBSTITUTE(B2,"\","@",$C$1-1),"\","|",$C$1-1))+1,
FIND("|",SUBSTITUTE(SUBSTITUTE(B2,"\","@",$C$1-1),"\","|",$C$1-1))-
FIND("@",SUBSTITUTE(SUBSTITUTE(B2,"\","@",$C$1-1),"\","|",$C$1-1))-1)
这个公式非常长,如果再考虑1级或末级科目容错的处理,那就更长了,这里不再展开。这里的重点就在于如何找到某级会计科目前后的“\”的位置?
(1)我们用SUBSTITUTE函数,先把某级科目前后的“\”替换成不同的特殊字符。
(2)再用FIND函数分别取得这两个特殊字符的位置m和n;m+1代表start_num,n-m-1代表num_chars。
(3)再用MID函数,取得中间级别科目的位置。
经典应用场景2:根据身份证号码提取出生日期,计算年龄
姓名 | 身份证号 | 出生日期 | 年龄 |
张三 | 320258197612253511 | =DATEVALUE(TEXT(MID(B2,7,8),"0000-00-00")) | =DATEDIF(C2,TODAY(),"Y") |
(1)出生日期,从身份证提取日期是有规律的(我们只讨论最新的18位身份证号码),即从第7位起的8个连续数字就是出生年月日。
(2)我们用MID函数从身份证号码中提取第7位起的8个数字。
(3)我们用TEXT函数把这8数字设置成“0000-00-00”的格式,这个格式 与日期格式“YYYY-MM-DD”相同,但它仍是文本,不是日期。
(4)我们再利用DATEVALUE函数,把“YYYY-MM-DD”格式的文本转换成日期。
(5)计算年龄用DATEDIF函数,参数“Y”,注意,小日期在前,大日期在后。
关于日期函数,可以参见Excel 函数公式/常用日期函数
正文完
我是安利达人
我的小店 | 自用产品,强烈推荐! | ||
白茶牙膏 | 洗洁精 | 洗衣液 | |
喜欢就点个赞、在看、留个言呗!
请先 后发表评论~