Excel Find and Replace manual/recorded differences with dates 1

[cerebalbore]

Hi All,

I have a spreadsheet exported from another application into Excel, which has a column with a 'date' in it which is in the format dd.mm.yyyy - this needs to then be formatted to a date for calculations etc. There are four of these column types and the rows regularly exceed 20,000, so for speediness I've selected the find and replace method.

As this is stored as text, when I do a find/replace via macro of "." to "/", Excel changes the date to m/d/y when the data in the cell is recognised as a date in the American format. Therefore the actual value in the cell is changing to the numeric representation and is no longer the original data - 8th February becomes 2nd August (and it should be the 8th of Feb).

I have tried several different ways to correct this via a macro, but what's confusing me is that when I do the following steps manually it works and I get the correct dates (dd/mm/yyyy). I have tried to use the recorder to capture the manual steps but when I run exactly the same code (recorded seconds before) it does the 'wrong' thing (or rather, not what I need).

These are the manual steps that give me the right dates:

1: Select Column
2: Change date format of column to "dd/mm/yyyy"
3: Find&Replace "." to "/"

Here's the code from the recorder:

Columns("AT:AT").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.ColumnWidth = 26.86

I guess my first question is:
What does the VB do that is different from me manually correcting the dates?

And my second:
How can I resolve this without looping through the data line by line?

Any help or advice is appreciated.

Many thanks

Kat


I'm such a noob

 

[SkipVought]

Hi,

Simply SELECT the DATA in each of the columns one at a time, Data > Text to columns > NEXT > NEXT > Select DMY and FINISH.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[cerebalbore]

Hi Skip,

Thanks very much for that - I never though of doing text to columns (I kind of get stuck in my ways).

I still don't understand what the differences are between what Excel does and what the VB does, but I think I'll just forget it and carry on!

Cheers.

Kat

I'm such a noob

 

[SkipVought]

You must be VERY CAREFUL about using a dd/mm/yyyy text structure. Your method could return unexpected results, as Excel defaults to mm/dd/yyyy, meaning that month and day would be switched.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]