text to columns date error when using Macro

Hi all,

I have recently come across a frustrating issue with Excel 2003 when using a macro to convert a large text field to columns.

Part of the field includes a date format (d/mm/yyyy h:mm).

Whilst recording the macro, it converts everything perfectly, including the date requirement.

However, when I run the macro on a fresh set of text, it converts the text to columns, but some - not all - of the date fields swap the day and month numbers around (i.e. as in the US date display), whilst the remaining dates revert back to text.

An extra catch is that the sheet reads the cells in the european date configuration, so the day becomes the month and the month becomes the day.

e.g. 12th Jan 2009 - displayed as 12/1/2009 when working properly, becomes 1/12/2009 (1st December 2009) when the error occurs.

So, my question....
Why does the text to column conversion work when done manually but won't work as a macro?

Does anyone know of a work around?

Rgds
Ant

Example of text I'm trying to convert to columns

{"12/01/2009 15:50:01 (T8******):", "I have spoken to.....(this line goes on for 6000+ characters).


Bit I need to keep is date and time reference and the T number - which is basically the last update time and user reference.

This particular date format changes to 1/12/09 15:50:01 - but still reads in the european time style so becomes 1st Dec 2009.

Sorry - blonde moment there

Attached (I hope) is a sample workbook with three lines of data (with commercially sensitive data removed).

Sheet 1 shows the base data
Sheet 2 shows the disired outcome of the macro
Sheet 3 shows the actual outcome of the macro

I hope this helps...

Cheers
Ant