王佩丰Excel基础教程24讲:第16讲-简单文本函数


3楼猫 发布时间:2024-12-20 03:37:42 作者:陈鹤轩biubiu Language

视频链接

一、使用文本截取字符串

1.Left函数

LEFT(字符串,[字符个数])

从一个文本字符串的第一个字符开始返回指定个数的字符

  • 获取“绰号”

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第0张

2.Right函数

RIGHT(字符串,[字符个数])

从一个文本字符串的最后一个字符开始返回指定个数的字符

  • 获取“后4位编码”

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第1张

身份证提取性别位数字(Right+Left)

15位身份证最后一个代表性别,18位倒数第二位代表性别,所以要判断身份证上性别必须截取17位。

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第2张

对于15位身份证,截取17位就是它的全部数字(与取姓名相似)

=RIGHT(LEFT(B13,17),1)

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

3.Mid函数

MID(字符串,开始位置,字符个数)

从文本字符串中指定的位置开始,返回指定长度的字符串

(1)获取“第3、4、5位编码”

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

(2)获取“姓名”

我们可以根据“取多不取少原则”来确定姓名的字符个数。在这种情况下,我们会从第4位开始多给几位字符,以确保不会漏掉任何可能的姓名。

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

二、获取文本中的信息

1.Find函数

FIND(要查找的字符串,被查找字符串,[开始位置])

返回一个字符串在另一个字符串中出现的起始位置。(区分大小写,且不允许使用通配符)

开始位置可选参数,指定开始查找的位置(默认为1,即从第一个字符开始查找)

(1)通过邮箱地址查找用户名和域名

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

用户名:在“@”的左边,所以我们要知道“@”在字符串中位数,位数-1为用户名长度

=LEFT(邮箱地址,FIND("@",邮箱地址)-1)

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

域名:在“@”右边部分,但是右边字符串长度不一致,所以采用Mid函数多取几位字符

=MID(邮箱地址,FIND("@",邮箱地址)+1,100)

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

2.Len与Lenb函数

(1)LEN 函数

  • 功能:返回字符串中的字符数(以字符为单位)。

  • 适用场景:主要用于处理单字节字符(如英文字母、数字)以及多字节字符(如中文、日文、韩文等),每个字符都被视为一个单位。

  • 语法:LEN(text)

  • text:要计算长度的字符串。

  • 示例:对于字符串 "Hello你好",=LEN("Hello你好") 返回 7,因为有 5 个英文字母和 2 个汉字,每个汉字也算作一个字符。

(2)LENB 函数

  • 功能:返回字符串的字节数(以字节为单位)。

  • 适用场景:在需要考虑字节长度的场合(如某些编码规则)使用。对双字节字符(如中文、日文、韩文等)进行特殊处理,每个双字节字符占 2 个字节,而单字节字符(如英文字符、数字等)占 1 个字节。

  • 语法:LENB(text)

  • text:要计算字节数的字符串。

  • 注意事项:

  • 在需要双字节字符支持的语言环境中(如中文或日文版Excel),LENB 会将双字节字符按 2 字节计算。

  • 如果在不支持双字节语言的环境中,LENB 的结果可能与 LEN 一样。

  • 示例:

对于字符串 "Hello你好",=LENB("Hello你好") 返回 11,因为 Hello 占 5 个字节,每个汉字占 2 个字节(共 4 个字节),总计 5 + 4 = 9。

(3)示例:获取数据中的单位

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

方法:中文为双字节字符,283元的字符长为4,字节长为5;7852千克字符长为6,字节长为8.那么我们可以知道lenb-len的长度为单位字符长度,公式 =RIGHT(A2, LENB(A2) - LEN(A2))

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

总结

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

在实际使用中,如果只需要字符个数,用 LEN;如果需要字节长度(如编码限制等场景),用 LENB。

三、关于身份证

1.身份证号码规则

(1)地区代码(前6位):

前6位数字表示身份证的地区编码,可以根据行政区划表查询所属地区。

(2)出生日期(第7-14位):

这8位数字表示出生年份、月份和日期(如19760517表示1976年5月17日)。

(3)性别(第17位):

第17位数字表示性别,奇数为男性,偶数为女性。

(4)校验码(第18位):

第18位是校验码,用于验证身份证号码的合法性。校验码可以是数字或字母“X”,通过一系列计算公式得出。

2.示例

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

(1)地区代码(前6位):

公式:

=LEFT(B2, 6)

根据提取的地区代码,可以通过行政区划表对照查询地区名称。

(2)出生日期(第7-14位):

公式:

=MID(B2, 7, 8)

将返回格式为 YYYYMMDD 的日期,比如 19760517。

如果想转换为标准日期格式,可以使用:

=DATE(LEFT(MID(B2, 7, 8), 4), MID(MID(B2, 7, 8), 5, 2), RIGHT(MID(B2, 7, 8), 2))

(3)性别(第17位):

公式:

IF(MOD(MID(B2, 17, 1), 2), "男", "女")

  • MOD(MID(B2, 17, 1), 2) 判断第17位数字是奇数还是偶数,奇数为男性,偶数为女性。

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

(4)校验码(第18位):

校验码的计算规则

校验码由以下公式计算:

步骤 1:确定加权因子

每一位数字都有一个对应的加权因子:

加权因子 = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2}

对应位置的数字和加权因子相乘后加总。

步骤 2:计算加权和

取身份证号码前17位,与加权因子一一对应相乘,并求和:

加权和 = (第1位 × 7) + (第2位 × 9) + (第3位 × 10) + ... + (第17位 × 2)

步骤 3:计算校验码索引

对加权和取模(%)11,结果是一个 0 到 10 之间的数字:

校验码索引 = 加权和 % 11

步骤 4:匹配校验码

根据索引值查表得到校验码:

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张

校验码可以是数字 0-9 或字母 X(表示10)。

步骤 5:比对校验码

将计算得到的校验码与身份证号码的第18位对比,如果一致,则身份证号码合法;否则为非法。

公式

=MOD(SUM(MID(B2, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2}), 11)

与效验码对比即可

王佩丰Excel基础教程24讲:第16讲-简单文本函数-第3张


© 2022 3楼猫 下载APP 站点地图 广告合作:asmrly666@gmail.com