3.3　文本格式转换的实例

函数7：ASC函数（将全角字符转换为半角字符）

函数功能

ASC函数将全角（双字节）字符转换成半角（单字节）字符。

函数语法

ASC(text)

参数解释

text：表示为文本或包含文本的单元格引用。如果文本中不包含任何全角字符，则文本不会更改。

实例解析

实例119　修正全半角字符不统一导致数据无法统计问题

在如图3-28所示表格中，可以看到“中国舞”报名人数有两条记录，但使用SUMIF函数统计时只统计出总数为2。

出现这种情况是因为SUMIF函数以“中国舞(Chinese Dance)”为查找对象，这其中的英文与字符是半角状态的，而B列中的英文与字符有半角的也有全角的，这就造成了当格式不匹配时就找不到了，所不被作为统计对象。这种时候就可以使用ASC函数先一次性将数据源中的字符格式统一起来，然后再进行数据统计。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键，然后向下复制D2单元格的公式进行批量转换，如图3-29所示。

选中D列中转换后的数据，按Ctrl+C组合键复制，然后再选中B2单元格，在“开始”选项卡的“剪贴板”组中单击“粘贴”下接按钮，在下拉列表中单击“值”按钮，实现数据的覆盖粘贴，如图3-30所示。

完成数据格式的重新修正后，可以看到E2单元格中可以得到正确的计算结果了，如图3-31所示。

函数8：WIDECHAR函数（将半角字符转换为全角字符）

函数功能

WIDECHAR函数用于将字符串中的半角（单字节）字符转换为全角（双字节）字符。函数的名称及其转换的字符取决于读者的语言设置。对于日文，该函数将字符串中的半角（单字节）英文字母或片假名更改为全角（双字节）字符。

函数语法

WIDECHAR(text)

参数解释

text：必需。表示文本或对包含要更改文本的单元格的引用。如果文本中不包含任何半角英文字母或片假名，则文本不会更改。

实例120　将半角字符转换为全角字符

此函数与ASC函数是相反函数，它用于将半角字符转换为全角字符。如果当前数据中的英文字母或字符全半角格式不一，为了方便查找与后期的数据分析，也可以事先一次性更改为全角格式，如图3-32所示。具体应用环境可同ASC函数。

函数9：LOWER函数（将文本转换为小写形式）

函数功能

LOWER函数将一个文本字符串中的所有大写字母转换为小写字母。

函数语法

LOWER(text)

参数解释

text：必需。表示要转换为小写字母的文本。函数LOWER不改变文本中的非字母的字符。

实例解析

实例121　将文本转换为小写形式

选中B1单元格，在公式编辑栏中输入公式：

按Enter键即可将对应的文本字符串转换为小写形式。

将鼠标指针指向B1单元格的右下角并向下进行公式复制，即可将其他文本字符串中的文本转换为小写形式，如图3-33所示。

函数10：UPPER函数（将文本转换为大写形式）

函数功能

UPPER函数用于将文本转换成大写形式。

函数语法

UPPER(text)

参数解释

text：必需。需要转换成大写形式的文本。text可以为引用或文本字符串。

实例解析

实例122　将文本转换为大写形式

使用UPPER函数可以将任意文本转换为大写形式。

选中B2单元格，在公式编辑栏中输入公式：

按Enter键即可将第一条文本字符串转换为大写形式。

将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可将其他文本字符串中的文本转换为大写形式，如图3-34所示。

函数11：PROPER函数（将文本字符串的首字母转换成大写）

函数功能

PROPER函数将文本字符串的首字母及任何非字母字符之后的首字母转换成大写，并将其余的字母转换成小写。

函数语法

PROPER(text)

参数解释

text：必需。表示用引号括起来的文本、返回文本值的公式或是对包含文本（要进行部分大写转换）的单元格的引用。

实例解析

实例123　将每个单词的首字母转换为大写形式

单个设置字母的大小写比较麻烦，使用PROPER函数可以实现在Excel中一次性将每个单词的首字母转换为大写。

选中B2单元格，在公式编辑栏中输入公式：

按Enter键即可将第一组文本字符串的所有首字母转换为大写形式。

将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可将其他文本字符串中的首字母转换为大写形式，如图3-35所示。

函数12：DOLLAR函数（四舍五入数值，并添加千分位符号和$符号）

函数功能

DOLLAR函数依照货币格式，将小数四舍五入到指定的位数并转换成美元货币格式文本。使用的格式为“($#,##0.00_);($#,##0.00)”。

函数语法

DOLLAR(number,decimals)

参数解释

• 　number：表示数字、包含数字的单元格引用或是计算结果为数字的公式。
• 　decimals：表示十进制数的小数位数。如果decimals为负数，则number在小数点左侧进行舍入。如果省略decimals，则默认其值为2。

实例解析

实例124　将销售金额转换为美元货币格式

选中C2单元格，在公式编辑栏中输入公式：

按Enter键即可将销售额为“4598”的数字格式转换为$（美元）货币格式。

将鼠标指针指向C2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可将其他数字格式的销售额转换为$（美元）货币格式，如图3-36所示。

函数13：RMB函数（四舍五入数值，并添加千分位符号和￥符号）

函数功能

RMB函数依照货币格式将小数四舍五入到指定的位数并转换成文本。使用的格式为“(￥#,##0.00_);(￥#,##0.00)”。

函数语法

RMB(number, [decimals])

参数解释

• 　number：必需。表示数字、对包含数字的单元格的引用或是计算结果为数字的公式。
• 　decimals：可选。表示小数点右边的位数。如果decimals为负数，则number从小数点往左按相应位数四舍五入。如果省略decimals，则默认其值为2。

实例125　将销售额一次性转换为人民币格式

要求将B列中的销售金额都转换为C列中的带人民币符号的格式。

选中C2单元格，在公式编辑栏中输入公式：

按Enter键得出转换后的结果，拖动C2单元格右下角的填充柄向下复制公式可批量转换，如图3-37所示。

函数14：FIXED函数（将数字显示千分位符样式并转为文本）

函数功能

FIXED函数将数字按指定的小数位数进行取整，利用句号和逗号，以小数格式对该数进行格式设置，并以文本形式返回结果。

函数语法

FIXED(number,decimals,no_commas)

参数解释

• 　number：表示要进行舍入并转换为文本的数字。
• 　decimals：表示十进制数的小数位数。
• 　no_commas：表示一个逻辑值，如果为TRUE，则会禁止FIXED在返回的文本中包含逗号。

实例解析

实例126　解决因四舍五入而造成的显示误差问题

财务人员在进行数据计算时，小金额的误差也是不允许的，为了避免因数据的四舍五入而造成金额误差，可以使用FIXED函数来避免小误差的出现，可以更好地提高工作效率。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键即可得到与显示相一致的计算结果，如图3-38所示。

公式解析

=FIXED(B2,2)

将B2单元格中的数字转换为保留两位小数的文本数字。

函数15：BAHTTEXT函数（将数字转换为泰铢）

函数功能

BAHTTEXT函数是将数字转换为泰语文本并添加后缀“泰铢”。

函数语法

BAHTTEXT(number)

参数解释

number：表示要转换成文本的数字、对包含数字的单元格的引用或结果为数字的公式。

实例解析

实例127　将销售金额转换为B|（铢）货币格式文本

使用BAHTTEXT函数可以将表格中的数字转换为B|（铢）货币格式文本。

选中B2单元格，在公式编辑栏中输入公式：

按Enter键即可将销售额为“3245”的数字格式转换为B| （铢）货币格式。

将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可将其他数字格式的销售额转换为B|（铢）货币格式，如图3-39所示。

函数16：TEXT函数（设置数字格式并将其转换为文本）

函数功能

TEXT函数将数值转换为按指定数字格式表示的文本。

函数语法

TEXT(value,format_text)

参数解释

• 　value：表示数值、计算结果为数字值的公式或对包含数字值的单元格的引用。
• 　format_text：作为用引号括起的文本字符串的数字格式。通过单击“设置单元格格式”对话框中的“数字”选项卡的“类别”框中的“数字”“日期”“时间”“货币”或“自定义”并查看显示的格式，可以查看不同的数字格式。format_text不能包含星号（*）。

实例解析

实例128　返回值班日期对应在的星期数

表格是一份值班统计表，要求返回值班日期对应在的星期数。

选中C2单元格，在公式编辑栏中输入公式：

按Enter键得出结果。

选中C2单元格，拖动该单元格右下角的填充柄向下填充，可以得到其他值班人员的值班日期对应的星期数，如图3-40所示。

公式解析

=TEXT(B2,"AAAA")

将B2单元格中的日期转换中文表示的星期。

实例129　让计算得到金额显示为“余款：15,850.00”形式

在计算未收金额时，想让计算得到金额显示为“余款：15,850.00”形式。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键得出结果。

选中D2单元格，拖动右下角的填充柄向下复制公式，即可按指定格式得到多项计算结果，如图3-41所示。

公式解析

=TEXT(B2-C2,"!余!款!：0,000.00")

先求B2与C2单元格的差值，然后将差值转换为带千分位符并包含两位小数且前面带上中文字为“余款：”的这种形式。

实例130　按上下班时间计算加班时长并显示为“*小时*分”形式

如图3-42所示为一份加班人员的工作表，想统计加班人员的加班时长，如果直接将加班结束时间减去开始时间，得到的结果，如图3-42所示。现在想结果显示为“*小时*分”的形式，则可以使用TEXT函数来设置公式，具体操作步骤如下。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键得出结果。

选中D2单元格，拖动该单元格右下角的填充柄向下填充，可以得到批量结果，如图3-43所示。

公式解析

=TEXT(C2-B2,"h小时m分")

先求C2与B2单元格中两个时间的差值，并将差值转换成“2小时50分”的这种表示形式。

实例131　解决日期计算返回日期序列号问题

如图3-44所示为一份产品清单，可以通过生产日期及保质期使用EDATE函数来计算到期日期，如果不使用TEXT函数，直接计算到期日期，返回的是时间序列号，如图3-44所示。如果想得到正确显示的日期，可以使用TEXT函数来进行转换，具体操作步骤如下。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键得出结果。

选中D2单元格，拖动该单元格右下角的填充柄向下填充，可以得到批量结果，如图3-45所示。

嵌套函数

EDATE函数用于返回表示某个日期的序列号，该日期与指定日期（start-date）相隔（之前或之后）指示的月份数。

公式解析

①　以B2单元格日期为开始日期，返回的日期是加上C2中给定月份数后的日期。

②　将①步返回的日期转换为“2016-05-01”的这种日期格式。

实例132　让数据统一显示固定的位数

利用TEXT函数可以实现将长短不一数据显示为固定的位数，例如下图中在进行编码整理时希望将编码都显示为6位数（原编码长短不一），不足6位的前面用0补齐，即把A列中的编码转换成B列中的形式，如图3-46所示，具体操作步骤如下。

选中B2单元格，在公式编辑栏中输入公式：

按Enter键即可将A2单元格中的编码转换为6位数。

选中B2单元格，拖动该单元格右下角的填充柄向下填充，可以得到批量转换结果，如图3-47所示。

公式解析

=TEXT(A2,"000000")

A2为要设置格式的对象。"000000"为数字设置格式的格式代码。

实例133　让合并的日期显示正确格式

如图3-48所示，A列中显示的是工单日期，如果直接合并，日期将被显示为序列号（从D列中可以看到）。

此时需要按如下实例来完成单元格数据的合并。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键得出合并后的结果。

选中D2单元格，拖动该单元格右下角的填充柄向下填充，可以得到其他合并结果，如图3-49所示。

公式解析

①　将A2单元格中的数据转换为“2018-11-5”形式。

②　将①步结果与B2、C2单元格的数据相连接。

函数17：VALUE函数（将文本数字转换成数值）

函数功能

VALUE函数用于将代表数字的文本字符串转换成数字。

函数语法

VALUE(text)

参数解释

text：必需。表示带引号的文本，或对包含要转换文本的单元格的引用。

实例解析

实例134　解决文本型数字无法计算的问题

在表格中计算总金额时，由于单元格的格式被设置成文本格式，从而导致总金额无法计算，如图3-50所示。

选中C2单元格，在公式编辑栏中输入公式：

按Enter键，然后向下复制C2单元格的公式即可实现将B列中的文本数字转换为数值数据，如图3-51所示。

转换后可以看到，再在C8单元格中使用公式进行求和运算时即可得到正确结果了，如图3-52所示。

函数18：CHAR函数

函数功能

CHAR函数用于返回对应于数字代码的字符。函数CHAR可将其他类型的计算机文件中的代码转换为字符。

函数语法

CHAR(number)

参数解释

number：必需。表示1～255用于指定所需字符的数字。字符是该计算机所用字符集中的字符。

实例解析

实例135　返回数字对应的字符代码

若要返回任意数字对应的字符代码，可以使用CHAR函数来实现。

选中B2单元格，在公式编辑栏中输入公式：

按Enter键即可返回数字“100”对应的字符代码。

将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得到其他数字对应的字符代码，如图3-53所示。

函数19：CODE函数

函数功能

CODE函数用于返回文本字符串中第一个字符的数字代码。返回的代码对应于计算机当前使用的字符集。

函数语法

CODE(text)

参数解释

text：必需。表示需要得到其第一个字符代码的文本。

实例解析

实例136　返回字符代码对应的数字

使用CODE函数可以返回任意字符代码（数字代码范围为1～255）所对应的数字。

选中B2单元格，在公式编辑栏中输入公式：

按Enter键即可返回字符代码“[”对应的数字。

将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得到其他字符代码对应的数字，如图3-54所示。

函数20：UNICHAR函数

函数功能

UNICHAR函数用于返回给定数值引用的Unicode字符。返回的Unicode字符可以是一个字符串，如以UTF-8或UTF-16编码的字符串。

函数语法

UNICHAR(number)

参数解释

number：必需。表示代表字符的Unicode数字。当Unicode数字为部分代理项且数据类型无效时，UNICHAR返回错误值“#N/A”；当数字的数值超出允许范围或数字为零（0）时，则函数UNICHAR返回错误值“#VALUE!”。

实例解析

实例137　返回数字对应的字符

选中B2单元格，在公式编辑栏中输入公式：

按Enter键即可返回数字“58”对应的字符。

将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得到其他数字对应的字符，如图3-55所示。