如何将单个单元格分割成多行并添加另一行 [英] How to Split a single cell into multiple rows and add another row

问题描述

我有一个表有两列。日期和测试名称。
我想要发生的是一个单元格中的文本字符串被分成多行。此外，我需要与每组文本相关联的日期。我已经尝试文本列，然后转置，但它只能一次处理1组字符串，而不是整个数据集。

解决方案

循环通过列A然后循环通过它旁边的字符串。

结果将在列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 
设置rng =范围（A2 ：a& Lstrw）
 
对于每个c在rng.Cells 
设置SpltRng = c.Offset（，1）
 txt = SpltRng.Value 
原始= Split（txt，）
 
对于i = 0到UBound（Orig）
单元格（Rows.Count，D）。End（xlUp）.Offset（1）= c 
单元格（Rows.Count，D）。End（xlUp）.Offset（，1）= Orig（i）
下一个我
 
下一个c 
 
 End Sub 
  

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.

解决方案

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

这篇关于如何将单个单元格分割成多行并添加另一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！