Use two steps assuming that your two values are in A1 and A2.

In B1 enter:
=IF(LEN(A1)=8,A1,0&A1) and copy down
This will make the dates all 8 digits long by putting a zero in the front if
needed.

In C1 enter:
=RIGHT(B1,4) for the year
In D1 enter:
=MID(B1,3,2) for the month
In E1 enter:
=LEFT(B1,2) for the day

--
Gary's Student


"flow23" wrote:

I have date in CSV as 9122005(09 Dec 2005) and 13122005(13 dec 2005)..

when I do text to column.. the only fields that get converted are 13122005 (
the ones with 2 digits date...

any solution?