Michael Koerner
7/30/2009 6:53:00 PM
My bad, should have mentioned that I'm using 2007. will give you change a
shot. Thanks very much
--
Regards
Michael Koerner
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4A71BD03.A6C5EF95@verizonXSPAM.net...
change this line:
myStr = .Text
to:
myStr = replace(.Text,"-"," ")
But replace was added in xl2k.
If you're using xl97 (or have to support it):
myStr = application.substitute(.Text,"-"," ")
Michael Koerner wrote:
>
> Dave;
>
> That worked. Now how do I replace the - with a space. when I do a search
> and
> replace, it changes the whole thing back into a dd-mmm-yy which is
> different
> from the original dd-mmm-yyyy sequence.
>
> --
>
> Regards
> Michael Koerner
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4A706DD0.E7C36F15@verizonXSPAM.net...
> Try:
>
> Dim myRng As Range
> Dim myCell As Range
> Dim myStr As String
>
> Set myRng = Selection
> For Each myCell In myRng.Cells
> With myCell
> myStr = .Text
> .NumberFormat = "@"
> .Value = myStr
> End With
> Next myCell
>
> Michael Koerner wrote:
> >
> > Tried your solution. All I got in place of the dates was a bunch of
> > numbers.
> > Thanks
> >
> > --
> >
> > Regards
> > Michael Koerner
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:4A6F74DC.4248D47B@verizonXSPAM.net...
> > Select the range to fix first:
> >
> > Dim myRng as range
> > dim myCell as range
> >
> > set myrng = selection
> > myrng.numberformat = "@"
> > for each mycell in myrng.cells
> > mycell.value = mycell.text
> > next mycell
> >
> > .text is what you see in the cell (after formatting). If the column is
> > too
> > narrow, you could see ###'s and this will keep those ###'s--so be a
> > little
> > careful.
> >
> > Michael Koerner wrote:
> > >
> > > I have a sheet with a two columns containing dates. Sometimes it is
> > > the
> > > full
> > > date as an example 25-Jul-1936, or Jul-1936 or 1936 I would like to
> > > be
> > > able
> > > to convert that to text and maintain the same face value. Is that
> > > possible
> > > with some sort of macro?
> > >
> > > --
> > >
> > > Regards
> > > Michael Koerner
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
--
Dave Peterson