excel 第16讲：简单文本函数

目录

一、使用文本截取字符串

二、获取文本中的信息

三、关于身份证

四、关于使用文本截取字符串

1、简单使用 left()、right()、mid()函数截取字符串

2、结合find()函数截取字符串

3、结合 len() 和 lenb() 函数和 find()函数截取字符串

五、关于身份证的查找

---

一、使用文本截取字符串

1、Left函数

2、Right函数

3、Mid函教

4、示例使用MOD函数与文本函数,提取身份证号性别信息

二、获取文本中的信息

1、Len与Lenb函数

2、Find函数

三、关于身份证

1、通过身份证前六位判断地区

2、通过身份证计算出生年月日

3、通过身份证判断性别

4、身份证验证

四、关于使用文本截取字符串

1、简单使用 left()、right()、mid()函数截取字符串

left() 函数基于所指定的字符数返回文本字符串中的第一个或前几个字符。 

语法 
left(text,num_chars) 
text 是包含要提取字符的文本字符串。 
num_chars 指定要由 LEFT 所提取的字符数。 
￭num_chars 必须大于或等于 0。 
￭如果 num_chars 大于文本长度，则 LEFT 返回所有文本。 
￭ 如果省略 num_chars，则假定其为 1。  

right() 根据所指定的字符数返回文本字符串中最后一个或多个字符。 

语法 
right(text,num_chars) 
text 是包含要提取字符的文本字符串。 
num_chars 指定希望 right 提取的字符数。 

说明 
• num_chars 必须大于或等于 0。 
• 如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。 
• 如果忽略 num_chars，则假定其为 1。 

MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。 

语法 
MID(text,start_num,num_chars) 
Text 是包含要提取字符的文本字符串。 
Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。 
Num_chars 指定希望 MID 从文本中返回字符的个数。 

说明 
￭ 如果 start_num 大于文本长度，则 MID 返回空文本 。 
￭ 如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。 

￭ 如果 start_num 小于 1，则 MID 返回错误值 #VALUE!。 
￭ 如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。

（1）在红花会的名单上截取“江湖人称”字段的前三个字为“绰号”

（2）在产品标号处理的表中截取“产品编号”的最后四位为“后四位编码”

（3）如果要在红花会名单表上截取“江湖人称”的字符串中的姓名呢，这个时候就不能单独使用left()或者right()函数，因为姓名的个数有可能是2个有可能是3个，就是个数不一致。

但是“江湖人称”中的绰号数是固定的三位。

（4）在红花会名单表上截取“江湖人称”的字符串中的姓名

（5）结合left()和right()函数截取产品标号处理表中“产品标号”sh字符的后三个数字

先使用left()函数截取前五位，再用right()函数截取后3位

left(E3,5) = sh313 , right(left(E3,5),3)=313

（6）从身份证号中提取性别

身份证号一共18位。（老身份证一共15位）

• 前6位为地址码，表示编码对象常住户口所在县(市、旗、区)的行政区划代码。
• 接下来8为是出生日期码，表示编码对象出生的年、月、日，年、月、日分别用4 位、2 位、2位(不足两位加0)数字表示，之间不用分隔符。
• 接下来3为是顺序码：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性（就看倒数第二位即可）。
• 最后一位是校验码

有多种解法：

1）可以使用if函数

=IF(LEN(B13)=15,RIGHT(B13,1),LEFT(RIGHT(B13,2),1))
或者
=IF(LEN(B13)=15,RIGHT(B13,1),MID(B13,17,1))

 2）可以结合left()函数和right()函数

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

这样15位的身份证不满17也能使得最后一位为判断性别的数字，也使得18位的身份证截取后最后一位为判断性别的那位数字

2、结合find()函数截取字符串

FIND 用于查找其他文本字符串 (within_text) 内的文本字符串 (FIND_text)，并从 within_text 的首字符开始返回 

FIND_text 的起始位置编号。也可使用 SEARCH 查找其他文本字符串中的某个文本字符串，但是FIND和SEARCH 

不同，FIND 区分大小写并且不允许使用通配符。 

语法 
FIND(FIND_text,within_text,start_num) 
• Find_text 是要查找的文本。 
• Within_text 是包含要查找文本的文本。 
• Start_num 指定开始进行查找的字符。

within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。 

说明 
• 如果 FIND_text 是空文本 ()，则 FIND 会匹配搜索串中的首字符（即：编号为 start_num 或 1 的字符）。 
• FIND_text 中不能包含通配符。 
• 如果 within_text 中没有 FIND_text，则返回错误值 #VALUE!。 
• 如果 start_num 不大于 0，则返回错误值 #VALUE!。 
• 如果 start_num 大于 within_text 的长度，则返回错误值 #VALUE!。 

（1）从“邮箱地址”中截取“用户名”

=LEFT(F2,FIND("@",F2,1)-1)

（2）从“邮箱地址”中截取“域名”

3、结合 len() 和 lenb() 函数和 find()函数截取字符串

LEN() 返回文本字符串中的字符数。 

语法 
LEN(text) 
Text 是要查找其长度的文本。空格将作为字符进行计数。

LENB() 返回文本字符串中用于代表字符的字节数。此函数用于双字节字符。 （中文字符）

语法 
LENB(text) 
Text 要查找其长度的文本。空格将作为字符进行计数。 

（1）从“邮箱地址”截取“域名”

用字符串的总长度减去@字符所在的位置就是域名的长度，用right()函数在原始字符串截取

（2）计算含有单字节和双字节中的中文个数

=LENB(A2)-LEN(A2)

比如，a1=865千克，lenb(a1)=7，len(a1)=5，lenb(a1)-len(a1)=2，表示含有2个双字节（中文字符）

（3）从“数据”中提取“单位”

五、关于身份证的查找

1、根据“身份证号”查找“地区”

使用vlookup()函数查找，因为使用文本函数处理的值结果输出的也是文本类型也就是字符串类型，而地区码表中的地区号是数值类型的，所以left()*1转为数值类型

2、根据“身份证号”查找“生日”

1）使用date()函数

 2）使用text()函数（结果不是日期格式，是文本类型）（看似是日期，实际不是日期类型）