Separating Column Data

[StormStrikes]

I am new to VBA but studying and learning and trying to 'practice' as much as I can.

At work I have access to spreadsheets that I can copy and play with that have, in some cases, thousands of rows of data. One such spreadsheet I have been working with has Column A with a date and time in it:

Please Login or Register  to view this content.

The reason for this is that the spreadsheet is produced from the export of data from another program and it cannot be changed. What I have been trying to do is to take that column and use VBA code to split the date and time into their own column.

I have thus been unsuccessful. Ive been googling as well with some tidbits here and there but nothing that seems all that efficient nor very specific to my need.

There is duplicate data in all those rows, and some columns that I wont need once its all said and done. I have managed to write some code that gets rid of the duplicate data, extra columns, etc. I know, kids stuff to most, but I'm just trying to learn by doing at this point.

I cant upload the spreadsheet unfortunately as it has work related data on it, but if you have any questions I will do my best to answer them, but any help on being able to split out the data would be really helpful.

Thanks in advance.

[Olly]

try:

Please Login or Register  to view this content.

[StormStrikes]

That almost worked perfectly.

I failed to mention that there is a header row in the spreadsheet, but I can deal with that.

The only thing that did not work was the date column. Im getting #VALUE! in the entire column. As I step through it its happening with the line:

Please Login or Register  to view this content.

I thought (just trying to see if I could spot the issue with observation) that it may be happening because in that line the date is formatted with 'yy' and the Number format is set up with 'yyyy', so I changed that, but it had no effect. Otherwise, everything else works perfectly.

I appreciate your help and the time you took to write that code.

[Olly]

Please post a sample workbook with some of your date data, formatted exactly as it is in your real worksheet.

[StormStrikes]

I can provide much, as mentioned earlier, its work data and thus proprietary, but I have attached a portion of the spreadsheet that contains the date and time column.

Alerts-Sample.xls

[Olly]

Ah, US number format (this is why completing your profile with your location can be useful...)

Change the numberformats, in red:

Please Login or Register  to view this content.

[StormStrikes]

Ahhh yeah, sorry about that. When I stepped through the code I saw that it was evaluating 06/16/2014 as 16/06/2014 but it did not occur to me that could have been the issue.

Just the same, thank you for the time you invested in helping me out. It is very much appreciated. I learned something I doubt I would have learned otherwise.