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" wrote in message
...
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" 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

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" wrote in message
...
On Jul 28, 3:58 pm, "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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Worked like a charm. thank you very much

--

Regards
Michael Koerner


"Michael Koerner" wrote in message
...
My bad, should have mentioned that I'm using 2007. will give you change a
shot. Thanks very much

--

Regards
Michael Koerner


"Dave Peterson" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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