Separating text in one cell into individual cells

Originally Posted by mewingkitty

It can definitely be done, and you're not asking too much.
Heck if I have time today, and no one else has got around to it, I'll take a crack at it just for practice (I still have a lot to learn about this as well!). The cells all have exactly the same number of characters, so it could even be done with formulas.

mew!

EDIT:
One question, where are the D, HB, CHB, etc. coming from on the second page?

Hi Mew, I add these in myself. Once all the data is separated in each cell and 1 row. At the begining of the table just after Breakdown there is the D records (HB) - cell A36, at the end of each table there is a Total (Total : 203) in cell A860 and 2 cells below that another table begins with the same column titles. Its used to index the data so I know which table these came from. Another thing is it always starts D - HB, the D (CTB), then C (HB), then C (CTB). Hope this helps.

Insert a sheet called "Transferred" into your workbook. Switch back to your raw data sheet.

Run the following macro:

Sub combineDate()
    Dim strLong As String, i As Long, lRow As Long, lStart As Long
    Dim strAlpha As String, strCat As String, iGap As String
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To lRow
        If InStr(Cells(i, 1), "BREAKDOWN") > 0 Then
            lStart = i
        End If
    Next

    For i = lStart To lRow
        If InStr(Cells(i, 1), "Records") > 0 Then
            strAlpha = Left(Cells(i, 1), 1)
            strCat = Replace(Mid(Cells(i, 1), InStr(Cells(i, 1), "(") + 1, 3), ")", "")
        End If

        If strCat = "CTB" Then
            iGap = ""
        Else
            iGap = "  "
        End If

        If InStr(Cells(i, 1), "/") > 0 Then
            strLong = Cells(i, 1) & iGap & Cells(i + 1, 2) & " " & Cells(i + 2, 2) & " " & Cells(i + 3, 2)
            Worksheets("transferred").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = strLong
            Worksheets("transferred").Cells(Rows.Count, 1).End(xlUp).Offset(0, 10) = strAlpha
            Worksheets("transferred").Cells(Rows.Count, 1).End(xlUp).Offset(0, 11) = strCat

        End If



    Next

    Worksheets("Transferred").Activate
    Range("a:a").Select
    Application.DisplayAlerts = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
                            FieldInfo:=Array(Array(0, 2), Array(11, 2), Array(19, 1), Array(20, 2), Array(32, 1), _
                                             Array(55, 1), Array(80, 1), Array(119, 1), Array(143, 1), Array(145, 1))
    Application.DisplayAlerts = True
    Cells(1, 3).EntireColumn.Delete
    Range("d:g").NumberFormat = "yyyy-mm-dd hh:mm:ss.000"


End Sub

I didn't include any of the headings or the coloring or the info above the re-organized data.