Split a single cell at line breaks

Question

I have a spreadsheet that has data in a single cell separated by line breaks. I need to split the cell into separate rows so that I can remove some data and recombine. Like this:

Item	Status
285T1150-3	285T0680-1 1 Complete 285T1145-7//D 1 ATS-182 285T1146-1//D 1 Complete 363A4872P4 1 No Router

Convert to this:

Item	Status
285T1150-3	285T0680-1 1 Complete
285T1150-3	285T1145-7//D 1 ATS-182
285T1150-3	285T1146-1//D 1 Complete
285T1150-3	363A4872P4 1 No Router

This is the code I've been using:

        check_col = colArray(0)
        ColLastRow = Range(check_col & Rows.Count).End(xlUp).Row
        For Each Rng In Range(check_col & "1" & ":" & check_col & ColLastRow)
            If InStr(Rng.Value, vbLf) Then
                Rng.EntireRow.Copy
                Rng.EntireRow.Insert
                
                For i = 0 To UBound(colArray)
                    c = colArray(i)
                    
                    Set currentrng = Range(c & Rng.Row)
                    Set upperRng = currentrng.Offset(-1, 0)
                
                    upperRng.Value = Mid(currentrng.Value, 1, InStr(currentrng.Value, vbLf) - 1)
                    currentrng.Value = Mid(currentrng.Value, Len(upperRng.Value) + 2, Len(currentrng.Value))
                Next i
            End If
        Next

Which works perfectly. It just takes a very long time. Sometimes upwards of 5-8 minutes. Is there a way I can streamline this so that it runs a little faster?

Answer 1

I'd strongly recommend PowerQuery for the task at hand:

• Imagine the following sample data;

  

• Select data, then load it into PQ as per below GIF;

  

• PQ will open, follow the steps as below GIF;

  

• Close PQ and save changes. Result will look like: