使用文本函数清理数据

---

您从不同来源获得的数据很多都不是可供分析的形式。在本章中，您将了解如何准备文本形式的数据以进行分析。

最初，您需要清理数据。数据清理包括从文本中删除不需要的字符。接下来，您需要以进一步分析所需的形式构建数据。您可以通过以下方式执行相同操作 -

• 使用文本函数查找所需的文本模式。
• 从文本中提取数据值。
• 使用文本函数格式化数据。
• 使用文本函数执行数据操作。

从文本中删除不需要的字符

当您从其他应用程序导入数据时，它可能包含不可打印的字符和/或多余的空格。多余的空间可以是 -

• 前导空格，和/或
• 单词之间的额外空格。

如果您对此类数据进行排序或分析，您将得到错误的结果。

考虑以下示例 -

这是您获得的产品信息的原始数据，包含产品 ID、产品描述和价格。字符“|” 分隔每行中的字段。

当您将此数据导入 Excel 工作表时，它看起来如下 -

正如您所观察到的，整个数据都在一个列中。您需要构建此数据以执行数据分析。但是，最初您需要清理数据。

您需要删除数据中可能存在的所有不可打印字符和多余空格。为此，您可以使用 CLEAN 函数和 TRIM 函数。

• 选择单元格 C3 – C11。
• 键入 =TRIM (CLEAN (B3))，然后按 CTRL + Enter。

公式填写在单元格 C3 – C11 中。

结果如下所示 -

使用文本函数查找所需的文本模式

要构建数据，您可能必须进行某些文本模式匹配，基于此您可以提取数据值。为此目的有用的一些文本函数是 -

从文本中提取数据值

您需要从文本中提取所需的数据才能构建相同的数据。在上面的示例中，您需要将数据放入三列中 - ProductID、Product_Description 和 Price。

您可以通过以下方式之一提取数据 -

• 使用将文本转换为列向导提取数据值
• 使用文本函数提取数据值
• 使用快速填充提取数据值

使用将文本转换为列向导提取数据值

如果您的字段是：

• 由字符分隔，或
• 按列对齐，每个字段之间留有空格。

在上面的示例中，字段由字符“|”分隔。因此，您可以使用“将文本转换为列”向导。

• 选择数据。

• 将值复制并粘贴到同一位置。否则，“将文本转换为列”将函数而不是数据本身作为输入。

• 选择数据。

• 单击功能区“数据”选项卡下“数据工具”组中的“文本到列” 。

步骤 1 - 将文本转换为列向导 - 将出现第 1 步（共 3 步）。

• 选择分隔符。
• 点击下一步。

步骤 2 - 将文本转换为分栏向导 - 将出现第 2 步（共 3 步）。

• 在“分隔符”下，选择“其他”。

• 在“其他”旁边的框中，键入字符|

• 单击“下一步”。

步骤 3 - 将文本转换为分栏向导 - 将出现第 3 步（共 3 步）。

在此屏幕中，您可以在向导中选择数据的每一列并设置该列的格式。

• 对于“目标”，选择单元格 D3。

• 您可以单击“高级” ，在弹出的“高级文本导入设置”对话框中设置小数分隔符和千位分隔符。

• 单击“完成”。

您的数据会转换为列，显示在三列中 - D、E 和 F。

• 将列标题命名为 ProductID、Product_Description 和 Price。

使用文本函数提取数据值

假设数据中的字段既不由字符分隔，也不在每个字段之间有空格的列中对齐，则可以使用文本函数来提取数据值。即使在字段被分隔的情况下，您仍然可以使用文本函数来提取数据。

为此目的有用的一些文本函数是 -

您还可以根据手头的数据组合两个或多个这些文本函数，以提取所需的数据值。例如，使用LEFT、RIGHT和VALUE函数的组合或者使用FIND、LEFT、LEN和MID函数的组合。

在上面的例子中，

• 剩下第一个 | 的所有字符 指定名称 ProductID。

• 从第二个开始的所有字符 | 给出名称“价格”。

• 第一个 | 之间的所有字符 第二个| 命名为 Product_Description。

• 每个 | 前后各有一个空格。

观察此信息，您可以通过以下步骤提取数据值 -

• 找到第一个 | 的位置 -第一| 位置

  • 您可以使用FIND功能

• 找到第二个 | 的位置 -第二 | 位置

  • 您可以再次使用FIND功能

• 开始到（第一个 | 位置- 2）文本字符给出 ProductID

  • 您可以使用左功能

• ( First | Position + 2) 到 ( Second | Position - 2) 文本字符给出 Product_Description

  • 您可以使用 MID 功能

• (第二个 | 位置+ 2) 到文本的结束字符给出价格

  • 您可以使用 RIGHT 功能

结果如下所示 -

您可以观察到价格列中的值是文本值。要对这些值执行计算，您必须设置相应单元格的格式。您可以查看下面给出的部分来了解格式化文本。

使用快速填充提取数据值

使用 Excel Flash Fill是从文本中提取数据值的另一种方法。但是，只有当 Excel 能够在数据中找到模式时，此方法才有效。

步骤 1 - 在数据旁边为 ProductID、Product_Description 和 Price 创建三列。

步骤 2 - 从 B3 复制并粘贴 C3、D3 和 E3 的值。

步骤 3 - 选择单元格 C3，然后单击“数据”选项卡上“数据工具”组中的“快速填充”。ProductID 的所有值均已填充。

步骤 4 - 对 Product_Description 和 Price 重复上述步骤。数据已填满。

使用文本函数格式化数据

Excel 具有多个内置文本函数，可用于格式化包含文本的数据。这些包括 -

根据您的需要格式化文本的函数-

将数字转换和/或格式化为文本的函数-

将文本转换为数字的函数-

使用文本函数执行数据操作

您可能必须对数据执行某些文本操作。例如，如果组织中员工的登录 ID 更改为新格式，则可能需要根据格式更改进行文本替换。

以下文本函数可帮助您对包含文本的数据执行文本操作 -