excel函数:汉字转全拼_30天中30个Excel函数：29

excel函数:汉字转全拼

Yesterday, in the 30XL30D challenge, we jumped around a workbook, and opened Excel files and websites, by using the HYPERLINK function.

昨天，在30XL30D挑战赛中，我们使用HYPERLINK函数跳到一个工作簿中，并打开了Excel文件和网站。

For day 29 in the challenge, we'll examine the CLEAN function. Sometimes the data you get from a website, or in a download file, has some unwanted characters, and the CLEAN function can help you fix it. It doesn't do much heavy lifting though, and refuses to help with the mess that the kids make. This will be perfect for a lazy Sunday!

在挑战的第29天，我们将检查CLEAN函数。有时，您从网站或下载文件中获得的数据包含一些不需要的字符，并且CLEAN功能可以帮助您修复它。不过，它并没有做太多繁重的事情，并且拒绝帮助孩子们制造混乱。这对于一个懒惰的星期日来说将是完美的！

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

So, let's take a look at the CLEAN information and examples, and if you have other tips or examples, please share them in the comments.

因此，让我们看一下CLEAN信息和示例，如果还有其他提示或示例，请在评论中分享。

功能29：清洁 (Function 29: CLEAN)

The CLEAN function shows removes some non-printing characters from text -- characters 0 to 31, 129, 141, 143, 144, and 157.

清除功能显示从文本中删除了一些非打印字符-字符0到31、129、141、143、144和157。

您如何使用CLEAN？ (How Could You Use CLEAN?)

The CLEAN function can remove some non-printing characters from text , but not all of them. You can use CLEAN, or other functions when necessary, to:

CLEAN函数可以从文本中删除一些非打印字符，但不是全部。您可以使用CLEAN或其他必要的功能来：

Remove some non-printing characters
删除一些非打印字符
Replace non-printing characters in text
替换文本中的非打印字符

CLEAN语法 (CLEAN Syntax)

The CLEAN function has the following syntax:

CLEAN函数具有以下语法：

- text is any information from which you want the non-printing characters removed
  文本是您要从中删除非打印字符的任何信息
CLEAN(text)
清洁(文字)

清洁陷阱 (CLEAN Traps)

The CLEAN function only removes some non-printing characters from text -- characters 0 to 31, 129, 141, 143, 144, and 157.

CLEAN功能仅从文本中删除一些非打印字符-字符0到31、129、141、143、144和157。

For other non-printing characters, such as the non-breaking space character 160, you can use SUBSTITUTE to replace them with space characters, or empty strings.

对于其他非打印字符，例如不间断的空格字符160，可以使用SUBSTITUTE将其替换为空格字符或空字符串。

示例1：删除非打印字符 (Example 1: Remove non-printing characters)

The CLEAN function works to remove some non-printing characters, such as those in the 0-30 range of the ASCII character set. In this example, I added characters 9 and 13 to the original text string from C3.

CLEAN函数可删除一些非打印字符，例如ASCII字符集的0-30范围内的那些字符。在此示例中，我在C3的原始文本字符串中添加了字符9和13。

=CHAR(9) & C3 & CHAR(13)

= CHAR(9)＆C3＆CHAR(13)

The LEN function shows that the number of characters in cell C5 increased to 15, with those non-printing characters included.

LEN函数显示单元格C5中的字符数增加到15，其中包括那些非打印字符。

With the CLEAN function, in cell C7, those characters are removed, and the number of characters is reduced by 2, so it's back to the original 13 characters.

使用CLEAN功能，在单元格C7中，将这些字符删除，并将字符数减少2，因此将其恢复为原始的13个字符。

=CLEAN(C5)

=清洁(C5)

示例2：替换非打印字符 (Example 2: Replace non-printing characters)

For the characters that the CLEAN function can't remove, like characters 127 and 160, you can use the SUBSTITUTE function to replace them.

对于CLEAN函数无法删除的字符，例如127和160，可以使用SUBSTITUTE函数替换它们。

=SUBSTITUTE(E3,CHAR(C3),"")

= SUBSTITUTE(E3，CHAR(C3)，“”)

下载CLEAN功能文件 (Download the CLEAN Function File)

To see the formulas used in today's examples, you can download the CLEAN function sample workbook. The file is zipped, and is in Excel 2007 file format.

要查看当今示例中使用的公式，可以下载CLEAN函数示例工作簿 。该文件已压缩，并且为Excel 2007文件格式。