On Apr 23, 2:47 pm, "undrline" <u28594@uwe wrote:
I have to pull data from various databases, who have some crazy date formats.
In most cases, I have to drop the data into Excel anyway, so I created a
macro to "fix" all the dates to all behave the same, and appear the same:

Sub DateRemoveZeros()
Selection.Replace What:="01/", Replacement:="1/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="02/", Replacement:="2/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="03/", Replacement:="3/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="04/", Replacement:="4/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="05/", Replacement:="5/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="06/", Replacement:="6/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="07/", Replacement:="7/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="08/", Replacement:="8/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="09/", Replacement:="9/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="/", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "m/d/yyyy"
End Sub

But, suddenly, the bugger doesn't appear to be working. I have uploaded an
example to play with:http://write-me.org/screenshots/DateProblem.xls

Please help me enhance the VBA script. Thank you.

--
Message posted viahttp://www.officekb.com

I took a look at the spreadsheet, and the dates seem to look fine to
me. However, if you want some additional ways to do your
"replacement," consider searching the Date & Time category in the
function box (Menu Bar: Insert/Function) in order to use the existing
data with preset Excel functions. I'd also look up the right, left,
mid, len, search, and substitute functions. You could also create a
For...Next loop and "pick" out the day, month, and year data and put
it together in a date format. You could also do Text to columns (Menu
Bar: Data/Text to Columns...) and delimit by the "/" and then use the
date funciton to put it back together again. Just some ideas.

Matt