一个单元格内多行，拆分为多行

wdd1126

一个合同编号，对应多张发票，将其拆分为多行合同编号多行发票号码，如下表所示。将表1批量转换为表2

hcm19522

D2:D4=LEN(TRIM(SUBSTITUTE(B2,CHAR(10)," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(B2,CHAR(10)," "))," ",))+1

F7:F20=OFFSET(A$2,SUM(N(ROW(A1)>SUMIF(OFFSET($D$2,,,{1,2,3}),">0"))),)&""

G7:H20=TRIM(MID(SUBSTITUTE(OFFSET(B$2,SUM(N(ROW(B1)>SUMIF(OFFSET($D$2,,,{1,2,3}),">0"))),),CHAR(10),REPT(" ",99)),COUNTIF($E$7:$E7,$E7)*99-98,99))

High12345

VBA，你看是否可以：

Sub splitVal()

    Set sh1 = Sheets("±í1")
    Set sh2 = Sheets("±í2")
   
    x = sh1.Range("A65536").End(xlUp).Row
   
    With sh2
        For i = 2 To x
            a = sh1.Range(sh1.Cells(i, 1), sh1.Cells(i, 3))
            brr = VBA.Split(a(1, 2), Chr(10))
            crr = VBA.Split(a(1, 3), Chr(10))
            .Range("B65536").End(xlUp).Offset(1, 0).Resize(UBound(brr) + 1, 1) = Application.WorksheetFunction.Transpose(brr)
            .Range("C65536").End(xlUp).Offset(1, 0).Resize(UBound(crr) + 1, 1) = Application.WorksheetFunction.Transpose(crr)
            .Range(.Cells(.Range("A65536").End(xlUp).Row + 1, 1), .Cells(.Range("B65536").End(xlUp).Row, 1)) = a
        Next i
    End With
    MsgBox "结束"
End Sub