拆分Excel列并将数据复制到新行 [英] Split Excel Column and Copy Data into New Row

问题描述

我有一个包含20k个记录的电子表格。它包含列A - J.列D有多个条目用£分隔。我想将Column D数据与AC和EJ列中的数据一起分割成多行。

输入：

 蓝色长款汽车£摩托车£滑板硬榛子
绿色短房子£汽车软粉红色
红色热房£££££城堡中黄色
  

输出：

 蓝色长车硬榛子
蓝色长长的摩托车硬榛子
蓝色长的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列并将数据复制到新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！