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

Look at the NumberFormat Property on the Range Object to see how the data is
formatted, which then you can use the VBA.Format(<Value>,<Format>) to
convert the value to text in the same manner.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Michael Koerner" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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
>
>
>

On Jul 28, 3:58*pm, "Michael Koerner" <iam...@home.com> 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 beable
> to convert that to text and maintain the same face value. Is that possible
> with some sort of macro?
>
> --
>
> Regards
> Michael Koerner

Sub DateText ()
Dim DtStr as String
Dim Rng as Range
Dim R as Range

Set Rng = Range("A:B" NOTE: REPLACE WITH RANGE CONTAINING DATES)

For each R in Rng
DtStr = R.value
r.NumberFormat = "@"
r.value = DtStr
DtStr = ""
Next R

End Sub

Untested code, please backup your work before applying.


Steven

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

All this did was change 25-Jul-1936 to 25/07/1936 thanks.

--

Regards
Michael Koerner


"Steven B" <(E-Mail Removed)> wrote in message
news:3d7fa9aa-4545-4a1a-8dc8-(E-Mail Removed)...
On Jul 28, 3:58 pm, "Michael Koerner" <iam...@home.com> 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

Sub DateText ()
Dim DtStr as String
Dim Rng as Range
Dim R as Range

Set Rng = Range("A:B" NOTE: REPLACE WITH RANGE CONTAINING DATES)

For each R in Rng
DtStr = R.value
r.NumberFormat = "@"
r.value = DtStr
DtStr = ""
Next R

End Sub

Untested code, please backup your work before applying.


Steven

Tried your solution. All I got in place of the dates was a bunch of numbers.
Thanks

--

Regards
Michael Koerner


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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;

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > 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

My bad, should have mentioned that I'm using 2007. will give you change a
shot. Thanks very much

--

Regards
Michael Koerner


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > 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