当数据导入到Excel中时，根据提供数据的源应用程序的不同，它可以有多种格式。

例如，它可以包含客户或员工的姓名和地址，但这最终都是工作表一列中的连续文本字符串，而不是被分隔成单独的列，如姓名、街道、城市。

可以使用普通分隔符分割数据。分隔符通常是逗号、制表符、空格或分号。这个字符分隔文本字符串中的每个数据块。

使用分隔符的一个很大的优点是它不依赖于文本中的固定宽度。分隔符准确地指示在何处分割文本。

您可能需要分割数据，因为您可能希望使用地址的特定部分对数据进行排序，或者能够对特定组件进行筛选。如果数据在透视表中使用，可能需要将名称和地址作为其中的不同字段。

本文向您展示了八种方法，通过使用分隔符来指示拆分点，将文本拆分为所需的组件部分。

样本数据

下面的所有示例都将使用上述示例数据。下载示例文件以获得示例数据以及基于分隔符提取数据的各种解决方案。

分割文本的Excel函数

有几个Excel函数可用于分割和操作单元格中的文本。

离开函数

的左函数返回从文本左边算起的字符数。

语法

= LEFT(文本，[Number])

• 文本-这是您希望从中提取的文本字符串。它也可以是工作簿中的有效单元格引用。
• 数量[可选]-这是您希望从文本字符串中提取的字符数。该值必须大于或等于0。如果值大于文本字符串的长度，则返回所有字符。如果省略该值，则假定该值为1。

正确的函数

的正确的函数返回文本右侧的字符数。

语法

= RIGHT(文本，[Number])

参数的工作方式与左上面描述的函数。

发现功能

的找到函数返回指定文本在文本字符串中的位置。这可用于定位分隔符。注意，搜索是区分大小写的。

语法

= FIND (SubText, Text， [Start])

• 潜台词这是一个你想要搜索的文本字符串。
• 文本这是要搜索的文本字符串。
• 开始[可选]-搜索的起始位置。

LEN函数

的LEN函数将按字符数给出文本字符串的长度。

语法

= LEN(文本)

• 文本-这是一个文本字符串，您要确定其字符数。

提取数据与左，右，FIND和LEN函数

使用示例数据的第一行(B3)，可以组合这些函数，使用分隔符将文本字符串拆分为多个部分。

= find ("，"， b3)

您使用找到函数获取第一个分隔符的位置。这将返回值18．

= left (b3, find ("，"， b3) - 1)

然后您可以使用左函数提取文本字符串的第一个组件。

请注意,找到获取第一个分隔符的位置，但需要从中减去1，以便不包含分隔符字符。

这将返回Tabbie O 'Hallagan．

= right (b3, len (b3) - find ("，"， b3))

获取文本字符串的下一个组件则更加复杂。您需要使用上述公式从文本中删除第一个组件。

该公式获取原始文本的长度，找到第一个分隔符位置，然后计算在该分隔符之后的文本字符串中还剩下多少个字符。

的正确的函数然后截断第一个分隔符之前和包括第一个分隔符在内的所有字符，以便随着找到每个分隔符，文本字符串变得越来越短。

这将返回056丹尼斯帕克，Greda，克罗地亚，44273

现在可以使用找到定位下一个分隔符和左函数提取下一个组件，使用与上面相同的方法。

对所有分隔符重复操作，这将把文本字符串分割为多个组件部分。

作为动态数组的FILTERXML函数

如果您使用的是Excel for Microsoft 365，那么您可以使用FILTERXML函数来分割文本，输出为动态数组。

通过将分隔符改为XML标记，可以将文本字符串转换为XML字符串。这样你就可以使用FILTERXML函数提取数据。

XML标记是用户定义的，但在本例中，s将表示子节点，t将表示主节点。

= " < t > < >”和替代(B2 , ",", " <年代 >" ) & " < / t > "

使用上述公式将XML标记插入到文本字符串中。

< t > <年代>名称< / s > <年代>街< / s > <年代>城市< / s > <年代>国家< / s > <年代>邮编< / s > < / t >

这将返回示例中的上述公式。

注意，定义的每个节点后面都有一个带反斜杠的结束节点。这些XML标记定义文本每个部分的开始和结束，并有效地以与分隔符相同的方式工作。

t = ' (FILTERXML(“< > < >”和替代(B3 , ",", " <年代 >" ) & " < / t >”、“/ / s "))

上面的公式将把XML标记插入到原始字符串中，然后使用这些标记将项目分离到一个数组中。

如上所示，该数组将把每个项放入单独的单元格中。使用转置函数会导致数组水平溢出而不是垂直溢出。

FILTERXML函数拆分文本

如果您的Excel版本没有动态数组，那么您仍然可以使用FILTERXML函数来提取单个项。

t = FILTERXML(“< > < >”和替代(B3 , ",", " <年代 >" ) & " < / t >”、“/ / s”)

现在可以使用上面的方法将字符串分成几个部分FILTERXML公式。

这将返回第一个部分Tabbie O 'Hallagan．

t = FILTERXML(“< > < >”和替代(B3 , ",", " <年代 >" ) & " < / t >”、“/ / s[2]”)

要返回下一节，使用上面的公式。

这将返回文本字符串的第二部分056年丹尼斯公园．

您可以使用相同的模式返回示例文本的任何部分，只需更改[２]在公式中找到相应的。

Flash填充拆分文本

Flash Fill允许你放入一个你想要如何分割数据的例子。

你可以看看这个使用flash填充清理您的数据的指南为更多的细节。

然后选择你想要分割数据的第一个单元格，点击Flash Fill。Excel将填充示例中的其余行。

使用示例数据，输入的名字进细胞C2,然后Tabbie O 'Hallagan进细胞C3．

Flash填充应该自动从示例数据中填充剩余的数据名称。如果没有，你可以选择cellC4，然后单击Flash填补图标数据工具集团的数据选项卡的Excel ribbon。

类似地，您可以添加街进细胞D2，城市进细胞E2，国家进细胞F2,邮政编码进细胞G2．

分别选择后续的单元格(D2到G2)，并单击Flash Fill图标。其余的文本组件将被填充到这些列中。

文本到列分割文本的命令

此Excel功能可用于根据分隔符将单元格中的文本分割为多个部分。

1. 选择整个样本数据范围(B2:B12)。
2. 点击数据选项卡。
3. 点击文本列图标数据工具组和一个向导将出现，以帮助您设置文本将如何分割。
4. 选择分隔在选项按钮上。
5. 按下下一个按钮。

1. 选择逗号作为分隔符，并取消选中任何其他分隔符。
2. 按下下一个按钮。
3. 数据预览窗口将显示您的数据将如何分割。选择一个位置放置输出。
4. 点击完成按钮。

您的数据现在将显示在工作表的列中。

将数据转换为CSV文件

这将只使用逗号作为分隔符，因为CSV(逗号分隔值)文件依赖逗号分隔值。

打开Notepad并复制并粘贴示例数据到其中。您可以通过在Windows任务栏左侧的搜索框中输入记事本或在应用程序列表中找到它来打开记事本。

一旦您将数据复制到记事本中，通过使用保存它文件➜另存为从菜单中。输入后缀为。csv的文件名。例如Split Data.csv。

然后可以在Excel中打开该文件。在浏览器文件类型下拉框中选择csv文件，然后单击OK。您的数据将自动出现，每个组件都位于单独的列中。

VBA拆分文本

VBA是Excel背后的编程语言，它允许你编写自己的代码来操作数据，甚至创建自己的函数。

要访问Visual Basic编辑器(VBE)，您需要使用Alt+季．

Sub SplitText() Dim MyArray()作为字符串，Count As Long, i As Variant For n = 2到12 MyArray = Split(Cells(n, 2)， "，") Count = 3为每个i在MyArray Cells(n, Count) = i Count = 1 Next i Next n End Sub

点击插入在菜单栏中，单击模块．模块将出现一个新的窗格。粘贴上面的代码。

这段代码创建了一个名为MyArray．然后，它遍历示例数据(第2行到第12行)，并使用VBA Split函数填充MyArray．

split函数使用逗号分隔符，因此文本的每一部分都成为数组的元素。

一个计数器变量被设置为3，它表示列C，这将是要显示的分割数据的第一列。

然后，代码遍历数组中的每个元素，并用元素填充每个单元格。对于行，单元格引用基于n，对于列，单元格引用基于Count。

的变量数在每个循环中递增，以便数据在行中填充，然后向下填充。

Power Query to Split Text

Excel中的Power Query允许使用分隔符将列操作为分段。

相关文章:

• 功率查询简介
• 强大的查询提示和技巧
• 电源查询M代码介绍

首先要做的是定义数据源，即输入到Excel工作表中的示例数据。

点击数据选项卡，然后单击获取数据在获取和转换数据一组丝带。

点击从文件在第一个下拉菜单中，然后点击从工作簿在第二个下拉列表中。

这将显示一个文件浏览器。找到示例数据文件(打开的文件)并单击好吧．

将显示一个导航弹出窗口，显示工作簿中的所有工作表。单击有示例数据的工作表，就会显示数据的预览。

展开左侧窗格中的数据树，以显示现有数据的预览。

点击转换数据这将显示电源查询编辑器。

确保突出显示包含数据的单列。点击分离柱图标变换一组丝带。点击由分隔符在出现的下拉列表中。

这将显示一个弹出窗口，允许您选择分隔符。默认为逗号。

点击好吧数据将被转换成单独的列。

点击关闭和负载在关闭组的ribbon，一个新的工作表将被添加到您的工作簿中，其中的数据表采用新的格式。

将计算列转换为拆分文本

您可以使用Power Pivot通过使用计算列来分割文本。

点击力量主选项卡，然后单击添加到数据模型图标表组。

您的数据将自动检测和弹出将显示的位置。如果这不是正确的位置，那么可以在这里重新设置。

离开我的表格有标题在弹出框中未勾选复选框，因为我们也想分割标题。

点击好吧并将显示预览屏幕。

右键单击数据列的标题(Column1)，然后单击弹出菜单中的Insert column。这将插入一个可以输入公式的计算列。

= LEFT ([Column1]， FIND ("，"， [Column1]) - 1)

在编辑栏中，插入上面的公式。

它的工作方式与本文方法1中描述的函数类似。

这个公式将提供的名字组件内的文本字符串。

使用与第一个计算列相同的方法插入另一个计算列。

= LEFT (RIGHT ([Column1]， LEN ([Column1]) - LEN([计算列1])- 1)，FIND ("，"， RIGHT ([Column1]， LEN ([Column1]) - LEN([计算列1])- 1)- 1)

将上面的公式插入到编辑栏中。

这是一个复杂的公式，您可能希望使用几个计算列将其分成几个部分。

这将提供街组件的文本字符串。

可以继续修改公式，为文本字符串的所有其他组件创建计算列。

数据透视表的问题在于，它既需要文本值，也需要数字值。由于示例数据仅为文本，因此需要添加一个数值。

中单击第一个单元格添加一列列，并在编辑栏中输入公式=1。

这将使1的值一直加到这一列。点击数据透视表图标首页标签的缎带。

点击数据透视表在弹出菜单中。在第一个弹出窗口中指定透视表的位置，然后单击好吧．如果数据透视表字段窗格不会自动显示，请右键单击透视表骨架并选择显示字段列表．
点击计算列在字段列表中，并将这些放在行窗口。

我们的数据透视表现在将显示文本字符串的各个组件。

结论

如果您不知道如何将每个项提取到自己的单元格中，那么处理逗号或其他分隔符分隔数据可能会非常痛苦。

幸运的是，Excel有相当多的选项可以帮助完成这个常见的任务。

您喜欢用哪一种?