In the US, this works for me. However, I'm not sure about UK date system.
The '5' you see is the 3rd item down in the "Date" pull-down in the "Column
data format". (ie YMD).

[A1:A100].TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
FieldInfo:=Array(1, 5)

Again, don't know if it would work for you.
--
Dana DeLouis
Windows XP & Office 2003


wrote in message
ups.com...
Hi,
I have a spreadsheet with 1800 dates in one column. However, Excel
doesn't recognise them as a date. Why? Because they are in the
following format:

20050512

Which I know is yyyymmdd.

However, I cannot work out a way to convert these, in a macro, to
something that Excel recognises as a UK date (dd/mm/yyyy).

I've writtne a macro that rearranges the numbers, but the new data
doesn't recognise amiguous US/UK dates properly (ie is 20050512 the
fifth of December or the twelfth of May?):

Sub ChangeDate()
For Each c In Range("A2:A1801").Cells
' For each cell, re-arrange the numbers - last 2 digits, middle 2
and first 4, putting in a "/" between them
c.Value = Right(c.Value, 2) & "/" & Mid(c.Value, 5, 2) & "/" &
Left(c.Value, 4)
Next

Any help much appreciated....