Sub tt() Dim arr, arrtemp1, arrtemp2 Dim i As Long, j As Long, k As Long, l As Integer With Sheet1 j = .Cells(65536, 1).End(xlUp).Row arrtemp1 = .Range("A1:A" & j) ReDim arr(1 To j, 1 To 4) For i = 1 To j If InStr(arrtemp1(i, 1), "第") Then l = l + 1 k = 0 End If k = k + 1 arr(k, l) = arrtemp1(i, 1) Next Sheet2.[A1].Resize(j, 4) = arr End With End Sub