输入：
蓝色长款汽车£摩托车£滑板硬榛子
绿色短房子£汽车软粉红色
红色热房£££££城堡中黄色
输出：
蓝色长车硬榛子
蓝色长长的摩托车硬榛子
蓝色长的Sketeboard硬榛子
绿色短房子软粉红色
绿色短车手软粉红色
红色热房间中等黄色
红色热点中等黄色
红色热堡垒中等黄色
红色热卡尔中等黄色
您的帮助将不胜感激！
干杯，
杰克
解决方案
这是一个将按照指定拆分数据的方法。在代码中使用变量来设置范围，如果需要可以更改。
Sub SplitData（）
Dim ws作为工作表
Dim rng As Range
Dim data As Variant
Dim dataSplit（）As Variant
Dim i As Long，j As Long，k As Long，n As Long
Dim col As Long，cols As Long
Dim rws（）As String
Dim addr As String
Dim rw As Long
cols = 10'Column J
col = 4'列D
'假设活动的shsets包含数据
设置ws = ActiveSheet
'假设数据从A1和列开始A是连续的
设置rng = ws.Range（ws.Cells（1，cols），ws。[A1] .End（xlDown））
'获取数据到数组
data = rng
j = 1
'数据中的数字
addr = rng.Columns（col）.Address
rw = Evaluate（= SUM（LEN（& addr）-LEN（SUBSTITUTE（&addr，£，））））
'size destination array
ReDim dataSplit（1 To UBound（data，1）+ rw，1 To cols）
For i = 1 To UBound（data，1）
'如果包含£然后拆分它
如果InStr（data（i，col），£）> 0然后
'将几行复制到目标数组
rws = Split（data（i，col），£）
对于n = 0到UBound（rws）
对于k = 1到cols
dataSplit（j + n，k）= data（i，k）
下一个
dataSplit（j + n，col）= Trim（rws（n））
下一个
j = j + UBound（rws）+ 1
Else
'将一行复制到目标数组
对于k = 1到cols
dataSplit（j ，k）= data（i，k）
下一个
j = j + 1
结束如果
下一个
'将resut放回到表
rng.Resize（UBound（dataSplit，1），cols）= dataSplit
End Sub
I have a spreadsheet with 20k records. It contains columns A - J. Column D has multiple entries separated by £. I would like to split Column D data into multiple rows along with the data in columns A-C and E-J.
Input:
Blue Long Car £ Motorcycle £ Skateboard Hard Hazel
Green Short House £ Motel Soft Pink
Red Hot Room £ Yard £ Fort £ Castle Medium Yellow
Output:
Blue Long Car Hard Hazel
Blue Long Motorcycle Hard Hazel
Blue Long Sketeboard Hard Hazel
Green Short House Soft Pink
Green Short Motel Soft Pink
Red Hot Room Medium Yellow
Red Hot Yard Medium Yellow
Red Hot Fort Medium Yellow
Red Hot Casle Medium Yellow
Your help will be greatly appreciated!
Cheers,
Jack
解决方案Here's a method that will split the data as specified. Variables are used in the code to set the range so can be changed if required
Sub SplitData()
Dim ws As Worksheet
Dim rng As Range
Dim data As Variant
Dim dataSplit() As Variant
Dim i As Long, j As Long, k As Long, n As Long
Dim col As Long, cols As Long
Dim rws() As String
Dim addr As String
Dim rw As Long
cols = 10 ' Column J
col = 4 'column D
'Assuming the active shsets contains the data
Set ws = ActiveSheet
' Assuming data starts in A1 and column A is contiguous
Set rng = ws.Range(ws.Cells(1, cols), ws.[A1].End(xlDown))
' Get data into an array
data = rng
j = 1
' Count number of £ in data
addr = rng.Columns(col).Address
rw = Evaluate("=SUM(LEN(" & addr & ")-LEN(SUBSTITUTE(" & addr & ",""£"","""")))")
' Size destination array
ReDim dataSplit(1 To UBound(data, 1) + rw, 1 To cols)
For i = 1 To UBound(data, 1)
' if contains £ then split it
If InStr(data(i, col), "£") > 0 Then
' copy several rows into destination array
rws = Split(data(i, col), "£")
For n = 0 To UBound(rws)
For k = 1 To cols
dataSplit(j + n, k) = data(i, k)
Next
dataSplit(j + n, col) = Trim(rws(n))
Next
j = j + UBound(rws) + 1
Else
' copy one row into destination array
For k = 1 To cols
dataSplit(j, k) = data(i, k)
Next
j = j + 1
End If
Next
' put resut back into sheet
rng.Resize(UBound(dataSplit, 1), cols) = dataSplit
End Sub
这篇关于拆分Excel列并将数据复制到新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！