常用文本函数及其应用实例

常用文本函数及其应用实例
excelhome 什么是文本？在excel 中文本是指除数值、日期时间、逻辑值和错误值以外的所有值。

一个文本值是由n(0<=n<=32767)个字符组成的，因此文本也叫字符串。

字符是什么？是计算机可识别的单个符号。

分为单字节字符和双字节字符。

字节和字符的区别在于：字符相当于人，字节相当于凳子；人分胖瘦，胖人需要坐两个凳子才能坐下来，也就是说有的字符需要双字节才能表示。

普通的数字，大小写的英文字母都是单字节字符；中文，中文的标点符号，以及全角的字符这些都是双字节字符。

文本实例：“ABCD ”、“123”、“”、“一二三四”等等
Excel 中的文本函数有34个标准的文本函数，但涉及文本的函数不计其数。

此次课程所涉及到的文本函数有：
len()/lenb(),
mid()/midb(),
left()/leftb()/right()/rightb(),
find()/findb()/search()/searchb(),
substitute()/replace()/replaceb(),
trim()/clean(),
rept()
len()/lenb()函数：
LEN 返回文本字符串中的字符数，即返回“人数”
LENB 返回文本字符串中用于代表字符的字节数，即返回“凳子数” 函数返回值（结果）为整数数值
语法：LEN(TEXT)；LENB(TEXT)
TEXT 参数不一定非是文本格式，也可以是其他格式
文本值其测量结果是按文本本身进行测量的。

用LEN测量数值的长度，无论它的格式怎么变化，其测量结果是原数值的长度。

当表现形式为其他形式时其测量是转成数字型文本进行计算的。

用LEN测量日期格式的结果为5，实质也是数值，只是表现形式不同罢了，当日期格式转化为“常规”后就可以看到它的文本格式。

时间格式其测量结果是17位，日期和时间联合起来是16位，反而少了。

也就是说日期、时间是按其的数值转成文本进行计算的。

全角字符的测量结果是字节数是字符数的两倍。

逻辑值（在excel单元格中输入逻辑值时默认为居中格式）是按照字母的长度测量。

即：逻辑值转成“TRUE”、“FALSE”进行计算
可以通过=A1&””来查看这些值的转换结果
LEFT()/LEFTB()/RIGHT/RIGHTB()函数：
LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符（按人数返回左边前几个几个字符）。

LEFTB基于所指定的字节数返回文本字符串中第一个或多个字节的字符（按凳子数返回左边的前几个字符）。

函数返回值（结果）：文本
语法：LEFT(text, num_chars)
LEFTB(text, num_bytes)
text是包含要提取字符的文本字符串。

也就是原字符串。

num_chars指定希望left提取的字符数
num_bytes指定希望leftb提取的字节数。

num_chars/num_bytes必须大于或等于0；如果其数值大于文本长度则函数返回所有文本；如果省略其数值则默认为“1”；如果不是整数，则按其整数部分返回结果。

其数值为“1”时的书写方法为：left(A1)或者left(A1, 1)
其数值为“0”时的书写方法为：left(A1,)或者left(A1,0) Number参数的特性为：A，如果是数字型文本，函数内部会将它转换成数值；B,如果数值不是整数，先取整再计算。

RIGHT()/RIGHTB()和LEFT()/LEFTB()的使用方法一样！
例：查找字符串中中文字符的个数利用LEN/LENB函数的特性进行取值，中文字符
为双字节字符，而英文字符为单字节字符，用LENB函数测量字符串的结果减去LEN函数测量其的结果得到的就是双字节字符的个数。

那么由此可以引申为“查找非中文字符的个数”应该是len(A1)-(lenb(A1)-len(A1))
不过这样就有一个问题：如何将字符串中的中文字符和全角字符区分？问题的答案还在思考中……
MID()/MIDB()函数
MID返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

MINDB根据您指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。

函数返回值（结果）：文本
语法：MID（text, start_num, num_chars）
MIDB(text, start_num, num_bytes)
Text是包含要提取字符的文本字符串
Start_num是文本中要提取的第一个字符的位置。

文本中第一个字符的start_num为1，以此类推。

Num-chars指定希望MID从文本中返回字符的个数。

Num_bytes指定希望MIDB从文本中返回字符的个数（按字节）其中此函数的特性为：如果start_num大于文本长度，则mid返回空文本，即（“”）；如果start_num小于文本长度，且start_num加上num_chars超过了文本的长度，则mid只返回至多直到文本末尾的字符;
如果start_num小于1，则mid返回错误值#V ALUE!;
如果num_chars是负数，则mid返回错误值
如果num_bytes是负数，则midb返回错误值；
如果以上参数不是整数，则按其整数部分返回结果；
全部的参数都是必须的！至多可以省略成mid(text, start_num,)例：身份证号码的秘密
15位AAAAAAYYMMDDNNS 前6位也就是A代表地区，7—12位代表
出生日期，13-14位是一个序列号，15位代表性别，单是男，双是女（原来15位身份证号码的性别是最后一位呀！现在才知道自己原来弄错了，和18位的等同了！）
18位AAAAAAYYYYMMDDNNNSX 1-6位代表地区，7-14位代表出生日期，15-16位是一个序列号，17位代表性别单是男双是女，18位是一个校验位那么如何根据身份证号码提取出对应的地区号、出生日期、性别呢？
答：地区号采用
出生日期采用=mid(A1, 7, if(len(A1)=18,8,6))；或者利用mid函数参数的特性——数值大于文本长度则函数返回所有文本，=mid(A1,7, len(A1)/2.2) 性别，利用left/right函数参数的特性—取整计算，=right(left(A1, 17))
在利用函数公式的时候要考虑所有数据的情况再“右拉”或者“下拉”。

Find()/findb()/search()/searchb()函数
Find()/findb()在一个文本值中查找另一个文本值（区分大小写），查找比较严格；
Search()/searchb()在一个文本值中查找另一个文本值（不区分大小写），还可以用通配符来查找
函数的返回值（结果）：数值（整数）
语法：find(find_text, within_text, start_num)
Findb(find_text, within_text, start_num)
Search(find_text, within_text, start_num)
Searchb(find_text, within_text, start_num)
Search和searchb在查找文本时不区分大小写;
Search和searchb类似于find和findb，但find和findb区分大小写;
如果没有找到find_text，则返回错误值;
若果忽略start_num，则默认其为1;
如果start_num不大于0或者大于within_text的长度，则返回错误值;
在search中find_text是要查找的文本，可以在find_text中使用通配符，包括问号和星号，如果要查找真正的问号或星号，请在该字符前键入波形符（”~”）
SUBSTITUTE()函数
在文本字符串中用new_text替代old_text
函数返回值（结果）：文本
语法：substitute(text, old_text, new_text, instance_num)
instance_num为一数值，用来指定以new_text替换第几次出现的old_text。

如果指定了Instance_num，则只有满足要求的old_text被替换，否则所有的old_text将被new_text替换掉。

TRIM()/CLEAN()函数
trim()除了单词之间的单个空格外，清除文本中所有的空格。

函数返回值为文本
语法：trim(text)
clean()删除文本中不能打印的字符
函数返回值为文本
语法：clean(text)
REPT()函数
按照给定的次数重复显示文本。

可以通过函数rept来不断地重复显示某一文本字符串，对单元格进行填充。

函数返回值为文本
语法：rept(text, number_times)
text为需要重复显示的文本
number_times是指定文本重复次数的大于等于0的数。