Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

EXCEL VBA - Convert comma separated values to different columns

enter image description here

I have data like this

  A  |   B   |   C
-----|-------|-------
  1  |  ABC  | L,F,M
  2  |  PQR  | G,D,S

Required

  A  |   B   |  C
-----|-------|----- 
  1  |  ABC  |  L
  1  |  ABC  |  F
  1  |  ABC  |  M
  2  |  PQR  |  G
  2  |  PQR  |  D
  2  |  PQR  |  S

Thanks in advance, for the help.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Try this

Sub SplitData()
    Dim cArray As Variant
    Dim cValue As String
    Dim rowIndex As Integer, strIndex As Integer, destRow As Integer
    Dim targetColumn As Integer
    Dim lastRow As Long
    Dim ws As Worksheet

    targetColumn = 3 'column number with comma separated data
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'change Sheet1 to your data data

    destRow = 0
    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For rowIndex = 1 To lastRow
            cValue = .Cells(rowIndex, targetColumn).Value 'getting the cell with comma separated data
            cArray = Split(cValue, ",") 'splitting comma separated data in an array
            For strIndex = 0 To UBound(cArray)
                destRow = destRow + 1
                .Cells(destRow, 5) = .Cells(rowIndex, 1)    '5 represents Column E
                .Cells(destRow, 6) = .Cells(rowIndex, 2)    '6 represents Column F
                .Cells(destRow, 7) = Trim(cArray(strIndex)) '7 represents Column G
            Next strIndex
        Next rowIndex
    End With
End Sub

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...