Separating one column into two column excel formatting

evilgreenred 

Run the following macro. You can change the output column by changing the value of TargetCol in the code.

Sub SplitData()
    Const SourceCol = 2 ' column B
    Const TargetCol = 4 ' column D
    Dim SourceRow As Long
    Dim LastRow As Long
    Dim TargetRow As Long
    Dim LineNumber As Long
    Application.ScreenUpdating = False
    TargetRow = 1
    Cells(TargetRow, TargetCol).Resize(1, 2).Value = Array("Number", "Solution")
    LastRow = Cells(Rows.Count, SourceCol).End(xlUp).Row
    For SourceRow = 2 To LastRow
        If IsNumeric(Cells(SourceRow, SourceCol).Value) Then
            LineNumber = Cells(SourceRow, SourceCol).Value
        Else
            TargetRow = TargetRow + 1
            Cells(TargetRow, TargetCol).Resize(1, 2).Value = Array(LineNumber, Cells(SourceRow, SourceCol).Value)
        End If
    Next SourceRow
    Application.ScreenUpdating = True
End Sub