Text to Columns Fixed Width Issue

[Jenna282]

Hi. I've recorded a macro that should do text to columns on a date column. The dates are formatted 05/05/2011. When I recorded the macro I specified that the first three characters were to be discarded (so leaving 05/2011, which Excel then automatically takes to be 01/05/2011 - It strips the day out of the date so that all the dates fall on the first of their respective months). This is the macro that Excel came up with:

Please Login or Register  to view this content.

However, when I run it, it returns gibberish. 25/09/2012 should become 09/2012 (so 01/09/2012 in the cell) but it becomes 01/05/2012. I can normally record and fix a macro but this is just beyond me! I think I have identified what the problems are but I can't fix it. I can see two problems: Firstly, Excel (in the macro) seems to ignore all of the 0's in the cell (so in my example I think Excel takes 25/09/2012 to be just 25/9/2012). Secondly, I think despite the date format being the UK version (dd/mm/yyyy) I think the macro reads it as mm/dd/yyyy so 25/09/2012 becomes (after Excel has removed the zero) 9/25/2012. The macro recorded me stripping out the first three characters so it goes on to strip out 9/2 leaving just 5/2012 (which becomes 01/05/2012). I think therefore I'm looking for help on two counts: 1) to stop excel missing out the zeros in the macro and 2) to force the macro to read the date as dd/mm/yyyy not mm/dd/yyyy.

Any help would be muchly appreciated!

Thanks

[Jenna282]

Anyone? *Gentle bump..*

[OllieB]

Jenna, your analysis could very well be correct. Manipulating date fields can be tricky. You could format your date in the source column to yyyymmdd and that use TextToColumns to create two fields, the first one being 6 digits long and the second one two digits, and then drop the second one. That way it should not get confused on dd/mm or mm/dd

[OllieB]

Jenna,

Or paste this formula in a dummy column (assuming column A holds the current date that you want to convert). This formula will give you a proper date but always for the first of the month

Please Login or Register  to view this content.

and then use copy & paste special 'values' to replace the contents of the original column if that is what you need.

[OllieB]

Hi Jenna, any of these suggestions working for you?

[kvsrinivasamurthy]

use this code.

Please Login or Register  to view this content.

[Jenna282]

Thanks Ollie. I'll use the =date(.. as an interim measure. kvsrinivasamurthy: Please could you explain how the =Array(Array.. parameters work? Thanks

[kvsrinivasamurthy]

Array(0,1). Here 0 represents the starting point of data,1 represents general format of cells

Array(3, 1), Here 3 represents the break point of data,1 represents general format of cells

If you want to change the format of cells ,you have to change the second parameter.First parameter decides whre break is inserted.

I feel it is clear.
With Regards
KVS

[Jenna282]

Thanks kvsrinivasamurthy. Just for completeness (for anyone else reading this thread), I couldn't find a simple way to force excel to recognise the date as dd/mm/yyyy before it applied text to columns. I tried changing the array formats (as per kvsrinivasamurthy who very kindly explained it above) to try 4 instead of 1 (which I believe is dd/mm/yy) but with no success. In the end I opted for the date(.. solution provided by OllieB which is now working great.. Thanks both