我目前在一张纸上有这些数据
Col A Col B Col C
1 A angry birds, gaming
2 B nirvana,rock,band
我想要做的是将第三列中的逗号分隔条目拆分并插入新行,如下所示:
Col A Col B Col C
1 A angry birds
1 A gaming
2 B nirvana
2 B rock
2 B band
我确信这可以用 VBA 完成,但我自己无法弄清楚。
变体使用Scripting.Dictionary
Sub ttt()
Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
Dim x&, cl As Range, rng As Range, k, s
Set rng = Range([C1], Cells(Rows.Count, "C").End(xlUp))
x = 1 'used as a key for dictionary and as row number for output
For Each cl In rng
For Each s In Split(cl.Value2, ",")
dic.Add x, Cells(cl.Row, "A").Value2 & "|" & _
Cells(cl.Row, "B").Value2 & "|" & LTrim(s)
x = x + 1
Next s, cl
For Each k In dic
Range(Cells(k, "A"), Cells(k, "C")).Value2 = Split(dic(k), "|")
Next k
End Sub
source:
result:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)