[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Replace . with / in Excel giving inconsistent results

Posted on 2010-01-04
9
Medium Priority
?
276 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:Belazir
  • 4
  • 3
  • 2
9 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1107 total points
ID: 26169757
Pretty much anything in VBA defaults to US date format. I think your easiest solution is going to be to loop through each cell in the range and use CDate with the Replace function:


Dim rngCell As Range
    For Each rngCell In Selection
        rngCell.Value = CDate(Replace(rngCell.Text, ".", "/"))
    Next rngCell

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 26171772
Belazir,

Why the odd number of points on offer?

Patrick
0
 

Author Comment

by:Belazir
ID: 26185049
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26185093
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... :)
 
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26185136
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
0
 

Author Comment

by:Belazir
ID: 26376691
My admin sorted it by using something in the TextToColumns function I'd never seen before.  Seems to have done the trick.
0
 

Author Closing Comment

by:Belazir
ID: 31672302
Not quite what I was after but would work.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26378390
As a matter of interest how was my answer not what you were looking for? I told you why it was happening...
0
 

Author Comment

by:Belazir
ID: 26389476
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question