Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

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

excel - How to Split a single cell into multiple rows and add another row

I have a table that has two Columns. Date and Test Name. What I would like to happen is that the string of text in one single cell be separated into multiple rows. In addition, I need the date to be associated with each set of text. I have tried text to columns and then transpose, but it can only handle 1 set of string at a time and not the entire data set.

enter image description here

See Question&Answers more detail:os

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

1 Answer

0 votes
by (71.8m points)

Loop through Column A then loop through the string next to it.

Results will be in column D

    Sub ChickatAH()
    Dim rng As Range, Lstrw As Long, c As Range
    Dim SpltRng As Range
    Dim i As Integer
    Dim Orig As Variant
    Dim txt As String

    Lstrw = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & Lstrw)

    For Each c In rng.Cells
        Set SpltRng = c.Offset(, 1)
        txt = SpltRng.Value
        Orig = Split(txt, " ")

        For i = 0 To UBound(Orig)
            Cells(Rows.Count, "D").End(xlUp).Offset(1) = c
            Cells(Rows.Count, "D").End(xlUp).Offset(, 1) = Orig(i)
        Next i

    Next c

End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...