将单元格中的多个条目转换为多行 [英] Transforming multiple entries in a cell into multiple rows

问题描述

我有一个excel文件与乐队和他们的类型的名称。

列A：bandname。列B：流派。

b列可能包含多个条目，如pop / rock。我想把它分成两行。

示例：

• [1] Awesomeband |流行/摇滚

将被转换为

• [1] Awesomeband |流行


• [2] Awesomeband |摇滚

如何在excel中意识到？任何线索？感谢提前。

解决方案

如果写一些像@John Dirk Morrison这样的话，可以允许两种以上的流派。不适用于选择，只是Sheet1上的列A和B。

  Sub BandSplit（）
 Dim作为工作表：Set Sheet = ThisWorkbook.Worksheets（Sheet1）
 Dim类型（）As String 
 Dim Count As Integer 
 Dim Index As Integer 
 Dim First As Boolean 
 Dim Row As Integer：Row = 2'排除标题行
 
 Do 
类型=拆分（Sheet.Cells（Row，2），/）
计数= UBound（类型）
 First = True 
如果Count> 0然后
索引= 0 
 Do 
如果First = True然后
 Sheet.Cells（Row，2）.Value2 =类型（索引）
 First = False 
 Else 
 Sheet.Rows（Row）.EntireRow.Insert（xlShiftDown）
 Sheet.Cells（Row，1）.Value2 = Sheet.Cells（Row + 1，1）.Value2 
 Sheet.Cells（Row，2）.Value2 =类型（索引）
结束If 
索引=索引+ 1 
如果索引> Count然后
退出Do 
结束如果
循环
结束If 
 Row = Row + 1 
如果Sheet.Cells（Row，1）.Value2 = 然后
退出Do 
结束If 
循环
 End Sub 
  

I have an excel file with the names of bands and their genre.

Column A: bandname. Column B: genre.

The b column might contain multiple entries such as "pop/rock". I would want to split that into two rows.

Example:

• [1] Awesomeband | Pop/Rock

would be transformed to

• [1] Awesomeband | Pop
• [2] Awesomeband | Rock

How would I realise that within excel? Any clues? Thanks in advance.

解决方案

Had a go at writing something like @John Dirk Morrison, allows for more than two genres. doesn't work with the selection though, just column A and B on Sheet1.

Sub BandSplit()
Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("Sheet1")
Dim Genre() As String
Dim Count As Integer
Dim Index As Integer
Dim First As Boolean
Dim Row As Integer: Row = 2 ' Exclude Header Row

Do
    Genre = Split(Sheet.Cells(Row, 2), "/")
    Count = UBound(Genre)
    First = True
    If Count > 0 Then
        Index = 0
        Do
            If First = True Then
                Sheet.Cells(Row, 2).Value2 = Genre(Index)
                First = False
            Else
                Sheet.Rows(Row).EntireRow.Insert (xlShiftDown)
                Sheet.Cells(Row, 1).Value2 = Sheet.Cells(Row + 1, 1).Value2
                Sheet.Cells(Row, 2).Value2 = Genre(Index)
            End If
            Index = Index + 1
            If Index > Count Then
                Exit Do
            End If
        Loop
    End If
    Row = Row + 1
    If Sheet.Cells(Row, 1).Value2 = "" Then
        Exit Do
    End If
Loop
End Sub

这篇关于将单元格中的多个条目转换为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！