Trouble importing a non delimited text file

[Liz_I3]

Hi
I am attempting to import a text file into excel. The text file is not comma delimited. It is from a mainframe output so it really is not delimited at all. When I import it into excel the whole file comes into column A. This would be ok but I am not sure how to separate it into separate fields from there. I need to turn the below 1 field into 5. They are Date, Time Description ID and Number. Using the first row as and example
The date field would be 10/03/05 , Time 06:50:05 Description would be every thing from the end of the time to ID. ID is ID#606 and the Number is 01010119-2. As you can see below all the rows are not set up exactly the same row 7 has no IDxxx. Some rows are total blanks. Other rows have banners etc the only row I am interested in are the ones that begin with a date.
Any Ideas would be appreciated. Is there a way to import it directly form the text file into the separate fields rather than importing it all into column A first?

Thanks
L

10/03/05 06:50:05 OP 8350 OPN-NORMAL OPEN ID #606 01010119-2

10/03/05 06:51:33 OP 8350 OPN-NORMAL OPEN ID #606 01010119-6

10/03/05 06:53:01 OP 8350 OPN-NORMAL OPEN ID #606 01010119-G

10/03/05 12:43:57 JSI 4180 CUSTMR CALLED STAT'N JOHN 01010119-1

10/03/05 12:43:57 COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE

10/04/05 01:29:42 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-1

10/04/05 01:31:10 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-2

 

[Pericic]

I believe you can simply do it by:
Data - Text To Columns - Delimited - Check the tab and space boxes

This will separate your data into the wanted columns

 

[Liz_I3]

No the Data - Text To Columns - Delimited - Check the tab and space boxes
just does not work for me except for my first two columns. But with a lot of experimenting and the help just_jon gave me in my last post I came up with this and it is working so far. I had to write one for each of my 3 columns. I run the first one then call the second then the third. Here is the first one which gives me field 3 in column D

Thank you very one for your help, I could not have done it without the help

L

Sub FilterMyWorld1Stp4()

Dim c As Range, Rng As Range, i As Integer
Dim st As String
Dim z As String

Application.ScreenUpdating = False
Set Rng = Range("C1:C" & Cells(Rows.Count, "c").End(xlUp).Row)
z = ActiveWorkbook.ActiveSheet.Name
st = "xx"
i = 2
For Each c In Rng
i = i + 1

If InStr(1, UCase(c.Value), "ID #") <> 0 Then
ActiveWorkbook.Worksheets(z).Cells(i, 4).Formula = "=MID(C" & i & ",1,search(" & Chr(34) & "ID" & Chr(34) & ",C" & i & ",1)-1)"
ElseIf InStr(1, UCase(c.Value), "9-") <> 0 Then
ActiveWorkbook.Worksheets(z).Cells(i, 4).Formula = "=MID(C" & i & ",1,search(" & Chr(34) & "9-" & Chr(34) & ",C" & i & ",1)-9)"
Else
ActiveWorkbook.Worksheets(z).Cells(i, 4).Value = ActiveWorkbook.Worksheets(z).Cells(i, 3).Value

End If
Next c
Application.ScreenUpdating = True
Set Rng = Nothing
End Sub