按分隔符将文本字符串拆分为多行 - 3 个快速技巧
通常,您可以使用文本分栏功能将单元格内容按特定分隔符(例如逗号、点、分号、斜线等)拆分为多列。但是,有时,您可能需要将分隔的单元格内容拆分为多行并重复其他列中的数据,如下图所示。 您有什么好的方法可以在 Excel 中处理此任务吗? 本教程将介绍一些在 Excel 中完成这项工作的有效方法。
要拆分由一些普通分隔符分隔的文本字符串,例如逗号、空格、分号、斜杠等,以下代码可以帮你一个忙。 请按照以下步骤操作:
备注: 这个代码 不 支持 解开,你最好在应用此代码之前备份数据。
第 1 步:打开 VBA 模块编辑器并复制代码
1. 激活您要使用的工作表。 然后,按 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
2. 在打开的窗口中,点击 插页 > 模块 创建一个新的空白模块。
3. 然后,将以下代码复制并粘贴到空白模块中。
VBA 代码:按特定分隔符(逗号、点、空格等)拆分文本
Sub SplitTextIntoRows()
'UpdatebyExtendoffice
Dim xSRg, xIptRg, xCrRg, xRg As Range
Dim xSplitChar As String
Dim xArr As Variant
Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
Dim xWSh As Worksheet
Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
If xSRg Is Nothing Then Exit Sub
xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
If xSplitChar = "" Then Exit Sub
Application.ScreenUpdating = False
xRow = xSRg.Row
xColumn = xSRg.Column
Set xWSh = xSRg.Worksheet
For xFNum = xSRg.Rows.Count To 1 Step -1
Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
xArr = Split(xRg, xSplitChar)
xIndex = UBound(xArr)
For xFFNum = LBound(xArr) To UBound(xArr)
xRg.EntireRow.Copy
xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
xIndex = xIndex - 1
Next
xRg.EntireRow.Delete
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
第二步:执行代码得到结果
1. 粘贴代码后,请按 F5 键来运行这段代码。 然后,会弹出一个提示框,提醒您选择包含要拆分的分隔文本的单元格,请参见屏幕截图:
2.然后,单击 OK,会弹出另一个提示框,提醒您输入拆分数据所依据的分隔符。 在这里,我输入一个逗号和一个空格 (, ),请看截图:
3.最后,单击 OK 按钮。 现在,您将看到所选文本字符串根据逗号拆分为行,其他相关列数据重复如下图所示:
如果您的单元格内容由换行符分隔,将它们分成多行,这是另一个可以帮助您的 VBA 代码。
备注: 这个代码 不 支持 解开 您最好在应用此代码之前备份数据。
第 1 步:打开 VBA 模块编辑器并复制代码
1。 按 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
2. 在打开的窗口中,点击 插页 > 模块 创建一个新的空白模块。
3. 然后,将以下代码复制并粘贴到空白模块中。
VBA代码:按换行符拆分文本
Sub SplitTextIntoRows()
'UpdatebyExtendoffice
Dim xSRg, xIptRg, xCrRg, xRg As Range
Dim xSplitChar As String
Dim xArr As Variant
Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
Dim xWSh As Worksheet
Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
If xSRg Is Nothing Then Exit Sub
xSplitChar = Chr(10)
Application.ScreenUpdating = False
xRow = xSRg.Row
xColumn = xSRg.Column
Set xWSh = xSRg.Worksheet
For xFNum = xSRg.Rows.Count To 1 Step -1
Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
xArr = Split(xRg, xSplitChar)
xIndex = UBound(xArr)
For xFFNum = LBound(xArr) To UBound(xArr)
xRg.EntireRow.Copy
xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
xIndex = xIndex - 1
Next
xRg.EntireRow.Delete
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
第二步:执行代码得到结果
1. 粘贴代码后,请按 F5 键来运行这段代码。 在弹出的框中,选择要拆分的单元格,见截图:
2.然后,单击 OK 按钮,所选单元格中的数据被分成几行,如下图所示:
如果你已经安装 Kutools for Excel,其 将数据拆分为行 功能,您可以按指定的任何分隔符将文本字符串拆分为多行。 请按照以下步骤进行:
第 1 步:选择“将数据拆分为行”功能
点击 库工具 > 合并与拆分 > 将数据拆分为行,请参见屏幕截图:
第 2 步:指定拆分的数据单元格和分隔符
在弹出的对话框中,使用以下选项:
现在,带有分隔文本字符串的选定单元格已根据特定分隔符转换为多行,请参见屏幕截图:
小贴士:如果要恢复原始数据,只需按 按Ctrl + Z 用于撤消。
使用方便? 对此功能感兴趣,请 点击下载即可免费试用 30 天.
如果您运行的是 Office 365 或 Excel 2016 及更高版本, 电源查询 是一个强大的工具,可以帮助您将分隔的文本拆分为多行或多列。 如果您希望在原始数据更改时刷新拆分数据,这将很有用。 请按照以下步骤完成它:
第 1 步:将数据表导入 Power Query
1. 选择您要使用的数据范围,然后单击 数据 > 从表,请参见屏幕截图:
小贴士:在 Excel 2019 和 Office 365 中,单击 数据 > 从表/范围.
2.在弹出的 创建表 对话框,单击 OK 按钮创建一个表,看截图:
3。 现在 电源查询 编者 窗口显示数据,见截图:
第 2 步:在 Power Query 中进行转换
1. 选择要拆分的列。 然后,单击 主页 > 拆分列 > 按分隔符,请参见屏幕截图:
2.在 按分隔符拆分列 对话框:
要用逗号、空格、分号等分割文本字符串,请按如下操作:
要通过链接中断将文本字符串拆分为多行,请按照以下步骤操作:
3. 现在,所选数据已拆分为多行,如下图所示:
第 3 步:将 Power Query 输出到 Excel 表格
1. 然后,您应该将数据输出到您的工作表中。 请点击 主页 > 关闭并加载 > 关闭并加载 / 关闭并加载到,(在这里,我将点击 关闭并加载),请参见屏幕截图:
小贴士:点击 关闭并加载 将数据输出到新的工作表; 点击 关闭并加载到 选项,数据将输出到您需要的任何其他工作表。
2.最后,数据将被加载到一个新的工作表中,见截图:
小贴士:如果您需要经常更新原表中的数据,请不要担心,只需右键单击结果表并单击 刷新 动态获取新结果。