First, my windows regional settings for time is:
hh:mm:ss tt
(hours, minutes, seconds, AM/PM)

When I did edit|replace manually (in xl2003), the space before the AM/PM was
changed. But when I looked at the formulabar, I saw times like:

06:12:20 PM
even though the cell was formatted: hh:mm:ss
and showed: 18:12:20

But if I changed my windows regional settings (via control panel) to not display
the AM/PM (I used a time format of: HH:mm:ss and HH means 24 hour time
display), then the value shown in the formulabar was 18:12:20 and there was no
space to get rid of.

So I think you have a couple of choices. You could do all the changes, then
change the "AM" or "PM" to " AM" or " PM".

Or if your data is all times, you could format the range as General, do the
change and then change it back to a time format.

======
Just an aside...

Try this:

with activecell
.numberformat = "General"
.value = now
msgbox .value & vblf & .value2
end with

You'll see a difference.


Sandy Mann wrote:

Using XL97.

If I enter 8:00 in A1, (formatted as h:mm), and then record a macro to
select A1 and replace a space with nothing, XL rightly tells me that if
cannot find any matching data to replace. However, if I then run the macro
that was recorded, (below), it changes the entry in A1 to 8:00:00AM ie it
removes the space between the 00 and the AM as if the entry had been 8:00:00
AM

Two questions:
1. I thought that formatting was just a mask over the *real* data in the
cell which is what is displayed in General format. If that is the case then
A1 would have held 0.33333333333333, so why does the VBA Replace find a
space? I suppose that the *Replace* in VBA is not the same *Replace* as the
one on the Edit menu but surely the data is 0.33333333333333 not a text
8:00:00 AM.

Sub Macro1()
Range("A1").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False
End Sub

(I know that if I change it to LookAt:=xlWhole then it will not replace the
space but I can't understand how it happens in VBA but not manually.)

2. Does this just happen in XL97 or all versions?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

--

Dave Peterson