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

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

Excel 实战：英文人名的姓、名称呼提取

某高校聘请了几位外国教授进行短期讲学，因为其姓名方面比较混乱，现在需要对其信息进行统计，即将专家的名称分为3部分，并根据性别输出称呼。下面通过具体步骤来详细介绍如何对专家信息进行统计。

STEP01：新建一个空白工作簿，重命名为“专家信息统计表”，切换至“Sheet1”工作表，并输入原始数据，如图12-63所示。

图12-63　原始数据

STEP02：将“名”从全名中分离出来，在C2单元格中输入公式“=LEFT(A2,FIND(” “,A2)-1)”，然后按“Enter”键返回即可得到相应的专家名，如图12-64所示。在此公式中利用FIND函数查找第1个空格，然后返回空格前面的部分。

STEP03：选中C2单元格，使用填充柄工具向下复制公式至C6单元格，完成对C3:C6单元格区域的自动填充，结果如图12-65所示。

STEP04：对“姓”进行提取，在D2单元格中输入公式“=RIGHT(A2,LEN(A2)-FIND(“!”,SUBSTITUTE(A2,” “,”!”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))))”，然后按“Enter”键返回，得到的结果就是外国专家的姓，如图12-66所示。

STEP05：选中D2单元格，使用填充柄工具向下复制公式至D6单元格，完成对D3:D6单元格区域的自动填充，结果如图12-67所示。

STEP06：通过性别输出称呼，在E2单元格中输入公式“=IF(B2=”男”,CONCATENATE(D2,”先生”),CONCATENATE(D2,”女士”))”，然后按“Enter”键返回即可得出称呼，结果如图12-68所示。

STEP07：选中E2单元格，使用填充柄工具向下复制公式至E6单元格，完成对E3:E6单元格区域的自动填充，最终结果如图12-69所示。

图12-64　“名”的提取

图12-65　自动填充后的结果

图12-66　“姓”的提取

图12-67　D列填充结果

图12-68　通过性别输出称呼

图12-69　最终结果

Excel 函数实战：从身份证种提取公司员工出生日期

打开“员工信息表.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图12-56所示。某办公人员需要从该工作表中提取公司员工的出生日期，以便计算工龄。下面通过具体的操作步骤来详细讲解该综合应用案例。

图12-56　原始数据

一般来说，身份证号码的第7位～14位数字为出生日期，可以先用MID函数返回身份证号码中出生日期数值，再使用TEXT函数把出生日期数值转换成数值文本格式，最后使用REPLACEB函数替换出生年份，得出具体的出生日期。

STEP01：选中B2单元格，在编辑栏中输入公式“=MID(A2,7,8)”，然后按“Enter”键即可返回数值“19831004”，如图12-57所示。

STEP02：选中B2单元格，利用填充柄工具向下复制公式至B20单元格，即可返回所有出生日期的数值，如图12-58所示。

图12-57　返回出生日期数值

图12-58　返回所有出生日期的数值

STEP03：选中C2单元格，在编辑栏中输入公式“=TEXT(B2,”0000-00-00″)”函数，按“Enter”键即可返回“1983-10-04”数值文本，如图12-59所示。

STEP04：选中C2单元格，利用填充柄工具向下复制公式至C20单元格，即可返回所有出生日期的文本，如图12-60所示。

图12-59　返回出生日期文本

图12-60　返回所有出生日期文本

STEP05：选中D2单元格，在编辑栏中输入公式“=REPLACEB(C2,1,5,” “)”函数，按“Enter”键即可返回出生日文本“10-04”，如图12-61所示。

STEP06：选中D2单元格，利用填充柄工具向下复制公式至D20单元格，即可返回所有出生日文本，如图12-62所示。

图12-61　返回出生具体日期

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

Excel 内容替换：SUBSTITUTE函数详解

SUBSTITUTE函数用于将字符串中的部分字符串用新字符串替换。其语法是：

SUBSTITUTE(text,old_text,new_text,instance_num)

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

打开“SUBSTITUTE函数.xlsx”工作簿，本例中的原始数据如图12-51所示。在“Sheet1”工作表中可以看到在编写例题步骤文本中含有“Enter”字符串，为规范编辑格式，要求使用SUBSTITUTE函数，将“Enter”字符串替换成“回车”字符串。具体的操作方法如下。

图12-51　原始数据

选中合并后的B11单元格，在编辑栏中输入公式“=SUBSTITUTE(B2,””Enter””,”回车”)”，然后按“Enter”键返回即可替换指定文本，如图12-52所示。

REPLACE函数用于将一个字符串中的部分字符用另一个字符串替换。其语法是：

REPLACE(old_text,start_num,num_chars,new_text)

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

打开“REPLACE函数.xlsx”工作簿，本例中的原始数据如图12-53所示。在实际应用过程中，财务人员需要使用REPLACE函数，将目录与页面之间的符号替换成“— —”符号。具体操作步骤如下。

图12-52　替换后的文本

图12-53　原始数据

STEP01：选中A8单元格，在编辑栏中输入公式“=REPLACE(A2,11,10,”— —”)”，然后按“Enter”键返回即可完成A2单元格中的文本替换，结果如图12-54所示。

STEP02：选中A8单元格，利用填充柄工具向下复制公式至A12单元格，实现所有文本的替换，最终结果如图12-55所示。

图12-54　替换部分文本

图12-55　替换所有文本

Excel 字符串大小写转换：LOWER、UPPER函数详解

LOWER函数用于将一个文本字符串的所有字母转换为小写形式。其语法是：

LOWER(text)

其中，text参数是要转换成小写字母的文本或字符串，或引用含有字符串的单元格。对其中非字母字符串不作转换。

UPPER函数用于将文本字符串中的字母全部转换成大写。其语法是：

UPPER(text)

其中，text参数是要转换成大写字母的文本或字符串，或引用含有字符串的单元格。下面通过实例具体讲解这两个函数的操作技巧。

在利用电子表格输入数据的时候，有时需要进行大小写字母之间的转换，需要将大写字母转换为小写字母，或者将小写字母转换为大写字母。下面通过实例具体讲解这两个函数的操作技巧。首先打开“大小写转换.xlsx”工作簿，本例中的原始数据如图12-46所示。

图12-46　原始数据

STEP01：选中C2单元格，在编辑栏中输入公式“=LOWER(B2)”，然后按“Enter”键即可返回B2单元格对应的小写字母，如图12-47所示。

STEP02：选中C2单元格，利用Excel的自动填充功能，复制公式至C3单元格，即可计算出B3单元格对应的转换数值，结果如图12-48所示。

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

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

STEP03：选中C4单元格，在编辑栏中输入公式“=UPPER(B4)”，然后按“Enter”键即可返回B4单元格对应的大写字母，如图12-49所示。

STEP04：选中C4单元格，利用Excel的自动填充功能，复制公式至C5单元格，即可计算出B5对应的转换数值，结果如图12-50所示。

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

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

Excel 美元货币符转换文本格式：DOLLAR函数详解

DOLLAR函数的功能是使用“$”（美元）货币格式，及给定的小数位数，将数字转换成文本。其语法是：

DOLLAR(number,decimals)

其中，number参数是数、数值的公式，或对含有数值单元格的引用；decimals参数是小数的位数。如果省略decimals，则假设其值为2；如果decimals为负数，则在小数点左侧进行舍入。下面通过实例具体讲解该函数的操作技巧。

打开“DOLLAR函数.xlsx”工作簿，本例中的原始数据如图12-42所示。要求使用DOLLAR函数，将工作表中的数值分别按整数、小数点后7位及小数点后两位转换成文本格式。具体操作步骤如下。

STEP01：选中B2单元格，在编辑栏中输入公式“=DOLLAR(A2,-2)”，然后按“Enter”键即可返回按整数转换的计算结果，如图12-43所示。

图12-42　原始数据

图12-43　返回A2单元格转换结果

STEP02：选中B3单元格，在编辑栏中输入公式“=DOLLAR(A3,7)”，然后按“Enter”键即可返回按小数点后7位转换的计算结果，如图12-44所示。

STEP03：选中B4单元格，在编辑栏中输入公式“=DOLLAR(A4,2)”，然后按“Enter”键即可返回按小数点后两位转换的计算结果，如图12-45所示。

图12-44　返回A3单元格计算结果

图12-45　返回A4单元格计算结果

Excel 半角字符转换为全角字符：WIDECHAR函数详解

WIDECHAR函数用于将单字节字符转换成双字节字符，即将半角英文字母转换为全角英文字母。语法是

WIDECHAR(text)

其中，text参数可以是文本，也可以是单元格。下面通过实例具体讲解该函数的操作技巧。

STEP01：新建一个空白工作簿，重命名为“WIDECHAR函数”。切换至“Sheet1”工作表，并输入原始数据，如图12-39所示。

图12-39　原始数值

STEP02：选中B2单元格，在编辑栏中输入公式“=WIDECHAR(A2)”，然后按“Enter”键即可返回A2单元格对应的全角文本计算结果，如图12-40所示。

STEP03：选中B2单元格，利用填充柄工具向下复制公式至B7单元格，通过自动填充功能将其他单元格中的字符转换成全角字符，最终结果如图12-41所示。

图12-40　返回A2对应的结果

图12-41　转换其他字符

如果文本中不包含任何半角英文字母，则WIDECHAR函数将不会更改文本。

Excel 全角字符转换为半角字符：ASC函数详解

ASC函数用于将双字节字符转换成单字节字符，即将全角英文字母转换为半角英文字母。其语法是：

ASC(Text)

其中，Text参数可以是文本，也可以是单元格。转换函数中只对双字节字符（全角英文字母）进行转换。下面通过实例具体讲解该函数的操作技巧。

STEP01：新建一个空白工作簿，重命名为“ASC函数”。切换至“Sheet1”工作表，并输入原始数据，如图12-36所示。

图12-36　原始数值

STEP02：选中B2单元格，在编辑栏中输入公式“=ASC(A2)”，然后按“Enter”键即可返回A2单元格对应的半角文本计算结果，如图12-37所示。

STEP03：选中B2单元格，利用填充柄工具向下复制公式至B7单元格，通过自动填充功能将其他单元格中的字符转换成半角字符，最终结果如图12-38所示。

图12-37　返回A2半角文本

图12-38　转换其他字符

Excel 应用TRIM函数删除空格

TRIM函数用于删除字符串中多余的空格，但会在英文字符串中保留一个作为词与词之间分隔的空格。其语法是：

TRIM(text)

其中，text参数是需要删除空格的文本字符串，或对含有文本字符串单元格的引用。下面通过实例具体讲解该函数的操作技巧。

打开“TRIM函数.xlsx”工作簿，本例中的原始数据如图12-33所示。为了规范工作表中英文字符的书写，需要使用TRIM函数将单元格中的多余空格删除，使工作表整体看起来更美观。具体的操作步骤如下。

图12-33　含有空格的单元格

STEP01：选中B1单元格，在编辑栏中输入公式“=TRIM(A1)”，然后按“Enter”键即可返回A1单元格中已删除空格的文本，如图12-34所示。

STEP02：选中B2单元格，在编辑栏中输入公式“=TRIM(A2)”，然后按“Enter”键即可返回A2单元格中已删除空格的文本，如图12-35所示。

图12-34　删除A1单元格文本中多余空格

图12-35　删除A2单元格中多余的空格

Excel 查找其他字符串值：FIND函数详解

FIND函数用于返回一个字符串在另一个字符串中出现的起始位置（区分大小写）。其语法是：

FIND(find_text,within_text,start_num)

其中，find_text参数为要查找的字符串，或对含有字符串单元格的引用；within_text参数为要在其中搜索的源文件；start_num参数为开始搜索的位置；within_text参数中第1个字符的位置为1，如果忽略则start_num=1。

此外，FINDB函数用法与FIND函数相同，只是后者还可用于较早版本的Excel版本，其语法是：

FINDB(find_text,within_text,start_num)

其中，find_text参数为搜索的文本；within_text参数为包含需要搜索文本的源文件；start_num参数是指定从哪一个字符开始搜索，下面通过使用FIND函数，来具体讲解其操作技巧。关于FINDB函数的使用，读者可以自行研究。

小明同学为表现对2012年世界杯的热爱，特意使用FIND函数将“2012年世界杯”每个字符返回在另一个字符串出现的起始位置，用所返回位置的数字表示“2012年世界杯”。那他是如何实现这一操作的呢？

STEP01：新建一个空白工作簿，重命名为“FIND函数”。切换至“Sheet1”工作表，并输入原始数据，如图12-23所示。

STEP02：选中B2单元格，在编辑栏中输入公式“=FIND(2,A2,1)”，然后按“Enter”键即可返回起始位置数字“1”，如图12-24所示。

STEP03：选中B3单元格，在编辑栏中输入公式“=FIND(0,A2,1)”，然后按“Enter”键即可返回起始位置数字“2”，如图12-25所示。

图12-23　原始数据

图12-24　返回第1个字符起始位置数字

STEP04：选中B4单元格，在编辑栏中输入公式“=FIND(1,A2,1)”，然后按“Enter”键即可返回起始位置数字“3”，如图12-26所示。

图12-25　返回第2个字符起始位置数字

图12-26　返回第3个字符起始位置数字

STEP05：选中B5单元格，在编辑栏中输入公式“=FIND(2,A2,1)”，然后按“Enter”键即可返回起始位置数字“1”，如图12-27所示。

STEP06：选中B6单元格，在编辑栏中输入公式“=FIND(“年”,A2,1)”，然后按“Enter”键即可返回起始位置数字“5”，如图12-28所示。

图12-27　返回第4个字符起始位置数字

图12-28　返回第5个字符起始位置数字

STEP07：选中B7单元格，在编辑栏中输入公式“=FIND(“世”,A2,1)”，然后按“Enter”键即可返回起始位置数字“6”，如图12-29所示。

STEP08：选中B8单元格，在编辑栏中输入公式“=FIND(“界”,A2,1)”，然后按“Enter”键即可返回起始位置数字“7”，如图12-30所示。

图12-29　返回第6个字符起始位置数字

图12-30　返回第7个字符起始位置数字

STEP09：选中B9单元格，在编辑栏中输入公式“=FIND(“杯”,A2,1)”，然后按“Enter”键即可返回起始位置数字“8”，如图12-31所示。

STEP10：选中B10单元格，在编辑栏中输入公式“=FIND(“会”,A2,1)”，然后按“Enter”键。因为在“2012世界杯”字符串中没有“会”这个字，所以返回错误代码“#VALUE!”，如图12-32所示。

图12-31　返回第8个字符起始位置数字

图12-32　返回不在字符中存在的字符位置数字

Excel 返回指定字符：CHAR函数详解

CHAR函数用于根据本机中的字符集，返回由代码数字指定的字符。其语法是：

CHAR(number)

其中，number参数是数字，对应返回的字符，此数字取值为1～255。下面通过实例具体讲解该函数的操作技巧。

图12-20　原密码工作表

打开“CHAR函数.xlsx”工作簿，本例中的原始数据如图12-20所示。为了隐藏用户的密码，可以将代表密码的数字改变成不经常使用的字符，这样既能起到一定的保密作用，还便于网络传输。下面将使用CHAR函数，将表格中的数字返回由代码数字指定的字符。具体操作步骤如下。

STEP01：选中D2单元格，在编辑栏中输入公式“=CHAR(C2)”，然后按“Enter”键即可返回指定的字符，如图12-21所示。

STEP02：选中D2单元格，利用填充柄工具向下复制公式至最后一个单元格处，通过自动填充功能返回所有指定字符，如图12-22所示。

图12-21　返回C2单元格数值指定的字符

图12-22　返回所有数字指定的字符