分类：文本与信息处理函数应用

文本函数是以公式的方式对文本进行处理的一种函数。文本函数主要处理文本中的字符串，也可对文本中单元格进行直接引用。信息函数则是用来获取单元格内容信息的函数。信息函数可以使单元格在满足条件的时候返回逻辑值，从而来获取单元格的信息，还可以确定存储在单元格中内容的格式、位置、错误类型等信息。本章就文本与信息处理函数的有关操作技巧展开论述。

Excel 英文名专家信息统计

外国人名统计的时候因为其姓名方面比较混乱，往往在如何称呼上容易出错，现在需要对其信息进行统计，即将专家的名称分为三个部分，并根据性别输出称呼，下面通过具体步骤来详细介绍如何对专家信息进行统计。

步骤1：输入高校专家信息，如图11-65所示。

步骤2：将名从全名中分离出来，在单元格C2中输入“=LEFT（A2，FIND（””，A2）-1）”，然后按Enter键，得到相应的专家名，如图11-66所示。在此公式中利用FIND函数查找第一个空格，然后返回空格前面的部分。

图11-65　原始数据

图11-66　“名”的提取

步骤3：选择单元格C2，拖动填充柄完成对单元格C3：C6的自动填充，结果如图11-67所示。

步骤4：对姓进行提取，在单元格E2中输入“=RIGHT（A2，LEN（A2）-FIND（”！”，SUBSTITUTE（A2，””，”！”，LEN（A2）-LEN（SUBSTITUTE（A2，””，””）））））”，然后按Enter键，得到的结果就是外国专家的姓，如图11-68所示。

步骤5：选择单元格D2，拖动填充柄，完成对其他专家姓的提取，结果如图11-69所示。

步骤6：通过性别输出称呼，在单元格E2中输入“=IF（B2=”男”，CONCATENATE（D2，”先生”），CONCATENATE（D2，”女士”））”，然后按Enter键，结果如图11-70所示。

图11-67　自动填充后的结果

图11-68　“姓”的提取

图11-69　自动填充后的结果

图11-70　中间名的提取

步骤7：选择单元格E2，拖动填充柄，完成对E3：E6的自动填充，最终结果如图11-71所示。

图11-71　最终结果

Excel 从身份证号种提取公司员工出生日期

某办公人员需要提取公司员工的出生日期，以便计算工龄。身份证号码从第7位~14位数字为出生日期，先用MID函数返回身份证出生日期数值，再使用TEXT函数将身份证出生日期数值转换成数值文本格式，最后使用REPLACEB函数替换出生年份，得出具体的出生日期。下面通过具体的操作步骤来详细讲解该案例。

步骤1：输入公司员工的身份证号码，然后根据需要输入其他相关数据字段，如图11-58所示。

图11-58　含有身份证号码的工作表

步骤2：选中B2单元格，在编辑栏中输入“=MID（A2，7，8）”，然后按Enter键，返回数值“19831004”，如图11-59所示。

图11-59　返回出生日期数值

步骤3：选中B2单元格，当鼠标指针移到单元格右下角变成黑色“+”时，按住左键拖动光标到单元格B20，返回所有数值，如图11-60所示。

图11-60　返回所有出生日期数值

步骤4：选中C2单元格，在编辑栏中输入“=TEXT（B2，”0000-00-00″）”函数，按Enter键，返回数值文本“1983-10-04”，如图11-61所示。

步骤5：选中C2单元格，当鼠标指针移到单元格右下角变成黑色“+”时，按住左键拖动光标到单元格C20，返回所有数值文本，如图11-62所示。

步骤6：选中D2单元格，在编辑栏中输入“=REPLACEB（C2，1，5，””）”函数，按Enter键，返回数值文本“10-04”，如图11-63所示。

步骤7：选中D2单元格，当鼠标指针移到单元格右下角变成黑色“+”时，按住左键拖动光标到单元格D20，返回所有数值文本，如图11-64所示。

图11-61　返回出生日期文本

图11-62　返回所有出生日期文本

图11-63　返回出生具体日期

图11-64　返回所有出生具体日期

REPLACE函数用于将一个字符串中的部分字符用另一个字符串替换，其语法是REPLACE（ole_text，start_num，num_chars，new_text）。其中，参数old_text为要将字符进行替换的文本；参数start_num为要替换new_text中字符在old_text中的位置；参数num_chars为要从old_text中替换的字符个数；参数new_text是来对old_text中指定字符串进行替换的字符串，下面通过实例具体讲解该函数的操作技巧。

步骤1：在实际应用过程中，财务人员需要使用REPLACE函数，将目录与页面之间的符号替换成“–”符号，按照要求输入本例的原始数据，如图11-55所示。

步骤2：选中A8单元格，在编辑栏中输入“=REPLACE（A2，11，10，”–“）”，然后按Enter键，替换文本，结果如图11-56所示。

图11-55　原始数据

图11-56　替换部分文本

步骤3：选中A8单元格，移动鼠标指针至单元格A12右下角处，当鼠标指针变成黑色“+”时，按住鼠标左键并拖动鼠标指针单元格A12，即替换所有文本，如图11-57所示。

图11-57　替换所有文本

注意：此函数适用于替换部分指定的字符串。替换与被替换的字符串属于同一工作表内。

Excel 实现文本替换：SUBSTITUTE函数

SUBSTITUTE函数用于将字符串中的部分字符串用新字符串替换，其语法是SUBSTITUTE（text，old_text，new_text，instance_num）。其中，参数text是包含要替换字符的字符串，或是对文本单元格引用；参数old_text是要被替换的字符串，如果原有字符串中的大小写不等于新字符串中的大小写，将不进行替换；参数new_text用于替换old_text的新字符串；参数instance_num是表示指定的字符串old_text在源字符串中出现几次，则用本参数指定要替换第几个，如果省略则全部替换，下面通过实例具体讲解该函数的操作技巧。

步骤1：某图书作者在编写例题步骤文本中含有“Enter”字符串，使用SUBSTITUTE函数，将“Enter”字符串替换成“回车”字符串，按照要求输入本例的原始数据，如图11-53所示。

图11-53　原始数据

步骤2：选中合并后的B11单元格，在编辑栏中输入“=SUBSTITUTE（B2，”Enter”，”回车”）”公式，单击Enter键，替换指定文本，如图11-54所示。

图11-54　替换后的文本

注意：此函数适用于将部分字符串以新字符串替换。

Excel 实现文本的大小写转换：LOWER和UPPER函数

LOWER函数用于将一个文本字符串的所有字母转换为小写形式，其语法是LOWER（text）。其中，参数text是要转换成小写形式的文本或字符串，或引用含有字符串的单元格。其中，对非字母字符串不作转换。UPPER函数用于将文本字符串中的字母全部转换成大写，其语法是UPPER（text）。其中，参数text是要转换成大写的文本或引用含有文本的单元格，下面通过实例具体讲解这两个函数的操作技巧。

步骤1：在利用电子表格输入数据的时候，有时需要进行大小写之间的转换，需要将大写字母转换为小写字母，或者将小写字母转换为大写字母。按照要求输入本例的原始数据，如图11-48所示。

图11-48　原始数据

步骤2：在C2单元格中输入公式：“=LOWER（B2）”，然后按Enter键，返回对应的小写字母，结果如图11-49所示。

图11-49　将B2单元格中的数据转换为小写

步骤3：利用Excel的自动填充功能，复制公式至C3单元格，即可计算出B3对应的转换数值，结果如图11-50所示。

图11-50　将B3单元格中的数据转换为小写

步骤4：在C4单元格中输入公式：“=UPPER（B4）”，然后按Enter键，返回对应的大写字母，结果如图11-51所示。

图11-51　将B4单元格中的数据转换为大写

步骤5：利用Excel的自动填充功能，复制公式至C5单元格，即可计算出B5对应的转换数值，结果如图11-52所示。

图11-52　将B5单元格中的数据转换为大写

Excel 查找并删除文本中的空格符：TRIM函数

TRIM函数用于删除字符串中多余的空格，但会在英文字符串中保留一个作为词与词之间分隔的空格，其语法是TRIM（text）。其中，参数text是需要删除空格的文本字符串，或对含有文本字符串单元格的引用，下面通过实例具体讲解该函数的操作技巧。

步骤1：某读者在杂志上发表了一篇文章，为了规范书写，使用TRIM函数将文章中的多余空格删除，使文章看起来更规范，按照要求输入本例的原始数据，如图11-45所示。

图11-45　含有空格的文本

步骤2：选中B1单元格，在编辑栏中输入“=TRIM（A1）”，然后按Enter键，返回已删除空格的文本，如图11-46所示。

图11-46　删除A1单元格文本中多余空格

步骤3：选中B2单元格，在编辑栏中输入“=TRIM（A2）”，然后按Enter键，返回已删除空格的文本，如图11-47所示。

图11-47　删除A2单元格中多余的空格

注意：该函数适用于将文本或段落转换成没有多余空格的标准格式。

Excel 在一个字符串值中查找另一个字符串值：FIND函数

FIND函数用于返回一个字符串在另一个字符串中出现的起始位置（区分大小写），其语法是FIND（find_text，within_text，start_num）。其中，参数find_text为要查找的字符串，或对含有字符串单元格的引用；参数within_text为要在其中搜索的源文件；参数start_num为开始搜索的位置；参数within_text中第一个字符的位置为1，如果忽略则start_num=1。

此外，FINDB函数用法与FIND函数相同，只是前者还可用于较早版本的Excel版本，其语法是FINDB（find_text，within_text，start_num）。其中，参数find_text为搜索的文本；参数within_text为包含需要搜索文本的源文件。start_num是指定从哪一个字符开始搜索，下面通过使用FIND函数来具体讲解其操作技巧，关于FINDB函数的使用，读者可以参考其他资料进行研究。

步骤1：某公司员工为表现对2012年世界杯的热爱，特使用FIND函数将“2012年世界杯”每个字符返回在另一个字符串出现的起始位置，用所返回位置的数字表示“2012年世界杯”，输入本例的原始数据，如图11-35所示。

步骤2：选中B2单元格，在编辑栏中输入“=FIND（2，A2，1）”，然后按Enter键，返回起始位置数字“1”，如图11-36所示。

图11-35　原始数据

图11-36　返回第一个字符起始位置数字

步骤3：选中B3单元格，在编辑栏中输入“=FIND（0，A2，1）”，然后按Enter键，返回起始位置数字“2”，如图11-37所示。

步骤4：选中B4单元格，在编辑栏中输入“=FIND（1，A2，1）”，然后按Enter键，返回起始位置数字“3”，如图11-38所示。

图11-37　返回第二个字符起始位置数字

图11-38　返回第三个字符起始位置数字

步骤5：选中B5单元格，在编辑栏中输入“=FIND（2，A2，1）”，然后按Enter键，返回起始位置数字“1”，如图11-39所示。

步骤6：选中B6单元格，在编辑栏中输入“=FIND（”年”，A2，1）”，然后按Enter键，返回起始位置数字“5”，如图11-40所示。

图11-39　返回第四个字符起始位置数字

图11-40　返回第五个字符起始位置数字

步骤7：选中B7单元格，在编辑栏中输入“=FIND（”世”，A2，1）”，然后按Enter键，返回起始位置数字“6”，如图11-41所示。

步骤8：选中B8单元格，在编辑栏中输入“=FIND（”界”，A2，1）”，然后按Enter键，返回起始位置数字“7”，如图11-42所示。

步骤9：选中B9单元格，在编辑栏中输入“=FIND（”杯”，A2，1）”，然后按Enter键，返回起始位置数字“8”，如图11-43所示。

步骤10：选中B10单元格，在编辑栏中输入“=FIND（”会”，A2，1）”，然后按Enter键，因为在“2012世界杯”字符串中没有“会”这个字，所以返回错误代码“#VALUE！”，如图11-44所示。

注意：本函数常用于检测一个文本是否包括所检测的字符串。

图11-41　返回第六个字符起始位置数字

图11-42　返回第七个字符起始位置数字

图11-43　返回第八个字符起始位置数字

图11-44　返回不在字符中存在的字符位置数字

Excel 由代码数字返回指定字符：CHAR函数

CHAR函数用于根据本机中的字符集返回由代码数字指定的字符，其语法是CHAR（number）。其中，参数number是数字，对应返回的字符，此数字介于1~255，下面通过实例具体讲解该函数的操作技巧。

步骤1：为隐藏用户的密码，将代表密码的两组数字分别改变为不经常使用的字符，以便于网络传输，起到一定的保密作用。下面使用CHAR函数，将表格中的数字变为由代码数字指定的字符，输入本例的原始数据，如图11-32所示。

图11-32　原密码工作表

步骤2：选中D2单元格，在编辑栏中输入“=CHAR（C2）”，然后按Enter键，返回指定的字符，如图11-33所示。

步骤3：选中D2单元格，移动鼠标指针至单元格右下角，当鼠标指针变成黑色“+”时，按住左键拖动光标到单元格D15处，返回所有指定字符，如图11-34所示。

图11-33　返回C2单元格数值指定的字符

图11-34　返回所有数字指定的字符

Excel 计算文本字符串中第一个字符的数字代码：CODE函数

CODE函数用于返回文本字符串中第一个字符在本机所用字符集中的数字代码，其语法是CODE（text）。其中，参数text是获取第一个字符代码的字符串，下面通过实例来具体讲解该函数的操作技巧。

步骤1：将文本中的名字用CODE函数返回第一个字符串的数字代码，以便于排序及检索，输入本例的原始数据，如图11-26所示。

步骤2：选中C2单元格，切换到“公式”选项卡，然后单击编辑栏前面的“插入函数”按钮，打开“插入函数”对话框，如图11-27所示。

图11-26　原始数据

图11-27　“插入函数”对话框

步骤3：单击下拉列表框，选中“文本函数”选项下的“CODE”函数选项，单击“确定”按钮，打开“函数参数”对话框，如图11-28所示。

步骤4：在“函数参数”对话框中的“Text”后面的选项框中输入参数“B2”，如图11-29所示。

步骤5：单击“确定”按钮，返回工作表，即可计算出单元格B2中第一个字符串的数字代码，如图11-30所示。

步骤6：选中B2单元格，移动鼠标指针至单元格右下角，当鼠标指针变成黑色“+”时，按住鼠标左键并拖动鼠标指针至最后一个单元格处，返回所有文本的第一个字符串的数字代码，如图11-31所示。

图11-28　“函数参数”对话框

图11-29　输入函数参数

图11-30　返回数字代码

图11-31　返回所有数字代码

注意：本函数适用于文本检索及排序。

Excel 删除文本中的所有非打印字符：CLEAN函数

CLEAN函数用于删除文本中所有非打印字符，其语法是CLEAN（text）。其中，参数text是即将删除非打印字符的字符串或文本，或是对含有非打印字符串单元格的引用，下面通过实例具体讲解该函数的操作技巧。

步骤1：从网页上复制一份“设备状况”报表，排版完毕后想将文章打印出来，但文章中含有非打印字符，在此使用CLEAN？函数删除工作表中非打印字符，该报表中的数据如图11-23所示，可以看到在“位置”及“状况”文本中含有非法字符，如图11-23所示。

图11-23　原始数据

步骤2：选中C3单元格，在编辑栏中输入“=CLEAN（C2）”，然后按Enter键，删除非打印字符，如图11-24所示。

图11-24　删除C2单元格中的非打印字符

步骤3：选中D3单元格，在编辑栏中输入“=CLEAN（D2）”，然后按Enter键，删除非打印字符，如图11-25所示。

图11-25　删除非打印字符后的文本

注意：此函数将文本中非打印字符全部删除，适用于任何需要打印的文本，也适用于任何领域。