Excel中如何将一个单元格里的内容按照一定规律拆分出来?

大家好，欢迎来到IT知识分享网。

Excel秘籍大全，前言

如何将一个单元格里面的内容按照一定规律拆分出来并且按照实际需求分类汇总好？正常而言，一个单元格通常只包含一个独立数据，然而实际工作中数据形态复杂多变，一个单元格里可能会包含很多数据内容，无形中给分析和处理数据增加了难度。如下图所示，要将图1的数据分类汇总成图2的形式，有什么简便高效的方式呢？

图1

图2

Excel秘籍大全，正文开始

先从常规的方法说起，如图3图4所示，选中B列，点击菜单栏数据——分裂，在弹出的文本分裂向导对话框中，选择“分隔符号”，点击“下一步”，分隔符号里选择其他，输入一个顿号，点击“下一步”，列数据类型选择常规，最后点击“完成”即可。

图3

图4

按照套餐类型选定分裂后的数据，如针对套餐1后分裂的数据，选中B1:E1，然后CTRL+C复制，在B列选择一个空的单元格，这里选的是B5单元格，然后鼠标右键——选择性粘贴——转置，如图5所示，从而将横向的数据文本转置粘贴为纵向的！

图5

CTRL+C复制“套餐1”文本所在的单元格区域即A1，再用鼠标左键按住不放选中A5:A8，按Ctrl+V粘贴，再将套餐2套餐3分裂后的数据按照以上步骤转置贴好，最终结果如图6所示。

图6

最后，选中C5单元格，输入以下公式：=CONCAT(IFERROR(–MID(B5,ROW($1:$10),1),””))，同时按住Ctrl+SHIFT+ENTER三键，然后向下填充完成即可，最终结果如图7所示。

图7

小包在这里给大家讲一下这个公式的原理，先用MID函数配合数组ROW($1:$10)，（数组的范围超过B列单元格数据最大的字符数就可以,这里取10），将B列单元格里面的字符分别提取出来，然后用双负号“—”将汉字转换为错误值#VALUE!，数字负负得正保持不变，再利用IFERROE函数将错误值转化为空值，最后利用CONCAT函数将数组中的数字结合到一起。

除了上述方式，还可以利用VBA达成同样的效果。选择开发工具——VB编译器，或者按快捷键ALT+F11，在弹出的对话框中，点击菜单栏插入-模块，输入以下代码点击运行即可（sheet30和sheet31大家根据实际工作表修改即可，sheet30为原数据所在的工作表，sheet31为数据拆分后所在的新工作表）：

Sub 拆分()

Dim i, irow, x, xrow As Integer

Dim arr

Dim cel As Range

Dim regexp As Object

irow = Sheet30.[a1].CurrentRegion.Rows.Count ‘获得源数据的行数

Sheet31.Range(“a:b”).Delete ‘语句循环时避免覆盖数据，sheet31为拆分后最终数据所在的工作表，大家以实际操作需求为准

For i = 1 To irow

arr = Split(Sheet30.Range(“b” & i), “、”) ‘将源数据b列每个有内容的单元格按照顿号拆分成一个从0开始的一堆数组

For x = 0 To UBound(arr) ‘数组的上标

xrow = Sheet31.Cells(Rows.Count, 2).End(xlUp).Row ‘结果工作表已使用单元格的行数

Sheet31.Cells(xrow, 2).Offset(1, 0) = arr(x) ‘把数组的值循环取出并赋值

Sheet31.Cells(xrow, 2).Offset(1, -1) = Sheet30.Cells(i, 1) ‘赋值套餐名称

Next

Next

Set regexp = CreateObject(“vbscript.regexp”) ‘创建一个正则表达式

With regexp

.Global = True

.Pattern = “[^0-9]” ‘匹配除数字以外的其他字符

For Each cel In Sheet31.Range(“b2:b” & Sheet31.Cells(Rows.Count, 2).End(xlUp).Row)

cel.Offset(0, 1) = .Replace(cel, “”) ‘遍历赋值

Next

End With

With Sheet31 ‘增加标题字段、居中对齐和自适应列宽

.[a1] = “套餐”

.[b1] = “菜名”

.[c1] = “价格”

.[a1].CurrentRegion.HorizontalAlignment = xlCenter

.Range(“b1”).EntireColumn.AutoFit

End With

End Sub

图8