Replace . with / in Excel giving inconsistent results
Posted on 2010-01-04
I have a data source that uses . as delimiters instead of /. If I do a manual Find/Replace to swap out the . for /, my dates now look like the dates they're supposed to be. However, if I do this in macro (and I've tried recording the macro and using the same code) it converts dates to US format and treats Jan 4th as April 1st.
I know plenty of other ways of doing this but for simplicity (and because I've a temp in doing this at the moment) I'd like to just figure out what's wrong with the recorded function and why it behaves differently. Recorded macro is below, points will go to the answer that requires the least change to what we're doing today...
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _