Hi, I am trying to work out a way where I can convert Text Values into a Date and Time Format through the use of a macro. Currently I have 3 files and in each files I have Date and Time Values that are showing as Text Values and rather than going through the Text to Columns Method or the Split (left, mid, right) Method, I was hoping somebody can advise any macro code that could do this for me quicker.

The ranges each differ but on average I could have around 150k rows of data for 1 file and this ranges from 3/4 columns on each file.

The following is an example of how the value is shown in the file.

09-DEC-12 12.56.03.695 AM

Currently I do find and replace to convert the . to : and than run the text to columns to remove the milliseconds this than converts the time to 24 hours and also gets rid of the AM/PM values but the problem is that this very time consuming.

Any help or advice would be really appreciated.

Thanks in advance.