Belazir
asked on
Replace . with / in Excel giving inconsistent results
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, _
ReplaceFormat:=False
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, _
ReplaceFormat:=False
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Patrick - catches people's attention.
Rorya - understand - what I'm trying to get at is that the VBA recording of the find and replace in the GUI doesn't do the same thing, and I don't get why. I know I can do an iteration through and I will fall back to that if necessary but I'd quite like to get it done using Replace only - if there's a way of doing this.
Rorya - understand - what I'm trying to get at is that the VBA recording of the find and replace in the GUI doesn't do the same thing, and I don't get why. I know I can do an iteration through and I will fall back to that if necessary but I'd quite like to get it done using Replace only - if there's a way of doing this.
As I said, anything in VBA defaults to US format. It's just the way it is (and always has been), as annoying as that is to those of us in the rest of the world... :)
Belazir,
>catches people's attention.
I guessed as much. However 497 would catch people's attention just as well and would be more generous. Mind you, there's nothing to suggest that you won't up the anti just before awarding the points!
Patrick
>catches people's attention.
I guessed as much. However 497 would catch people's attention just as well and would be more generous. Mind you, there's nothing to suggest that you won't up the anti just before awarding the points!
Patrick
ASKER
My admin sorted it by using something in the TextToColumns function I'd never seen before. Seems to have done the trick.
ASKER
Not quite what I was after but would work.
As a matter of interest how was my answer not what you were looking for? I told you why it was happening...
ASKER
I was looking for a one-liner rorya, that's all. No idea why TextToColumns works, when Replace goofs up despite it being recorded from a working function. Just one of those Microsoft oddities I guess.
Why the odd number of points on offer?
Patrick