Separating text in one cell into individual cells

Hi, thanks for the code, it works up to a point. For some reason it does not separate text to columns, the data is all in one cell, a message box appears after the Macro is run "Microsoft Visual Basic . X . 400". Also it does not include the other 3 cells directly below which need to be aligned to the same row and if possible the headings too, although none of the colouring is needed.

Thank you for taking the time to create this though as it still save me vast amounts of time as I do this a number of times during the week. Appreciate any/all other input in this. Thanks

I wrote one that had some kinks in it, but never finished it off because I saw someone else post here. I'll send it your way tomorrow if you still don't have an answer by then.

mew.

Eh
re-wrote one, it's pretty... crude.

Works, mostly, not sure what's happening with the format of the dates half way through though. So yeah, save a copy of the original. :P

mew

Thanks mdbct, this works and picks up all the data.

The only thing is that the date format for D:G does not seem to work on CTB records. When I try to insert a formula for cells E and F it does not seem to work for CTB records. Here is the formula :-

=INT(F2)-INT(E2)+1

The error message in the cell is #VALUE!.

Essentially I'm trying to join your code to my own whic does not seem to be working, I can post it up if you'd like. Otherwise any help on this is appreciated.

the formatting issue was because I forgot to specify the sheet whose columns were to be formatted.

The CTB issue is due to there being a space in front of the time, so Excel is interpreting it as a text value instead of a Date Time value.

Try this version.

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, lTransRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    lTransRow = 10
    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 InStr(Cells(i, 1), "/") > 0 Then
lTransRow = lTransRow + 1
Worksheets("Formatted Report").Cells(lTransRow, 1).NumberFormat = "@"
Worksheets("Formatted Report").Cells(lTransRow, 1) = Mid(Cells(i, 1), 2, 3)
Worksheets("Formatted Report").Cells(lTransRow, 2).NumberFormat = "@"
Worksheets("Formatted Report").Cells(lTransRow, 2) = Mid(Cells(i, 1), 12, 8)
Worksheets("Formatted Report").Cells(lTransRow, 3) = Mid(Cells(i, 1), 21, 3)
Worksheets("Formatted Report").Cells(lTransRow, 4) = Trim(Mid(Cells(i, 1), 33, 23))
Worksheets("Formatted Report").Cells(lTransRow, 5) = Trim(Mid(Cells(i, 1), 57, 23))
Worksheets("Formatted Report").Cells(lTransRow, 6) = Trim(Mid(Cells(i + 1, 2), 2, 23))
Worksheets("Formatted Report").Cells(lTransRow, 7) = Trim(Mid(Cells(i + 1, 2), 38, 23))
Worksheets("Formatted Report").Cells(lTransRow, 8) = Cells(i + 2, 2)
Worksheets("Formatted Report").Cells(lTransRow, 9) = Cells(i + 3, 2)
Worksheets("Formatted Report").Cells(lTransRow, 10) = strAlpha
Worksheets("Formatted Report").Cells(lTransRow, 11) = strCat
Worksheets("Formatted Report").Cells(lTransRow, 12).Formula = "=INT(F" & lTransRow & ")-INT(E" & lTransRow & ")+1"
       End If


Next

Worksheets("Formatted Report").Columns("D:G").NumberFormat = "yyyy-mm-dd hh:mm:ss.00"
Worksheets("Formatted Report").Columns("L").NumberFormat = "General"

End Sub

Addendum: I forgot to mention that I added your formula to the L column