Converting Text to Dates - TextToColumns

Hi all,

I have a bunch of worksheets that have dates saved as 'text' and not as a 'date'. I wrote a macro to correct this on a per-cell basis using DateValue(), but I was hoping to be able to use TextToColumns to do all the work, as it seems much simpler and quicker to me.

Unfortunately, I'm running into a problem with it working perfectly fine on most sheets but not all. I attached two small sample sheets - before I run my macro and after. On the next to last worksheet, the date format is not changed even though the macro correctly identifies it and attempts to change it (verified by stepping through the macro manually). Is there something I'm doing wrong here? I'm trying to get a final date format of day-month-year, but I found that I have to specify "mm-dd-yy" as the NumberFormat to get it to correctly show. Not sure why that is, either...

Please Login or Register  to view this content.

At the end, I'm not sure if there will be any issues with "Date" having a date number format or not so I figured I'd change it back to be safe.

Thanks for any help!

Hi,

Thanks, that works great! I hadn't used Find() before, so that's good to know how it works. It's also good to know that you don't have to Activate each sheet to run, which I had to do before.

If TextToColumns expects "mm-dd-yy", would it be best to convert the NumberFormat first? All of our dates are "dd-mm-yy", and we need to keep it as such in the end.

I also have one question about the FieldInfo bit - why did you choose to use Array(0,4) instead of Array(1,4)? The built-in help files don't have anything about a 0 XlColumnDataType, and when I recorded a macro to see how TextToColumns works, it generated Array(1,4).

Originally Posted by tehneXus

Weird, I recorded a macro too and it showed Array(0,4)

That is weird...

Ah well, thanks for the help!