I have a list of numbers formatted as text, for instance 1648-50, 1648-56,
0932-73, etc.

When I run the Replace function, trying to replace all the dashes with
periods (while keeping the same format--because I need the leading zeroes),
this happens:

0932-73 becomes 932.73, 1648-50 becomes 1648.5, and so on.

When I check the formatting of the cells, they're all still listed as
"Text", and this happens even if I specify that the number format of the
replacement should be text. What's the deal? Is there any way to keep this
from happening?

Are these in a single column?

If yes, then I'd insert an adjacent column and use a formula:

=substitute(a1,"-",".")

This will keep the value a string.

You can edit|copy, edit|paste special|values and delete the original column if
you want.

bill wrote:

I have a list of numbers formatted as text, for instance 1648-50, 1648-56,
0932-73, etc.

When I run the Replace function, trying to replace all the dashes with
periods (while keeping the same format--because I need the leading zeroes),
this happens:

0932-73 becomes 932.73, 1648-50 becomes 1648.5, and so on.

When I check the formatting of the cells, they're all still listed as
"Text", and this happens even if I specify that the number format of the
replacement should be text. What's the deal? Is there any way to keep this
from happening?

--

Dave Peterson

that worked perfectly.

thanks a lot!

"Dave Peterson" wrote:

Are these in a single column?

If yes, then I'd insert an adjacent column and use a formula:

=substitute(a1,"-",".")

This will keep the value a string.

You can edit|copy, edit|paste special|values and delete the original column if
you want.

bill wrote:

I have a list of numbers formatted as text, for instance 1648-50, 1648-56,
0932-73, etc.

When I run the Replace function, trying to replace all the dashes with
periods (while keeping the same format--because I need the leading zeroes),
this happens:

0932-73 becomes 932.73, 1648-50 becomes 1648.5, and so on.

When I check the formatting of the cells, they're all still listed as
"Text", and this happens even if I specify that the number format of the
replacement should be text. What's the deal? Is there any way to keep this
from happening?

--

Dave Peterson

You can also use custom formatting to get you leading zeroes

"bill" wrote:

I have a list of numbers formatted as text, for instance 1648-50, 1648-56,
0932-73, etc.

When I run the Replace function, trying to replace all the dashes with
periods (while keeping the same format--because I need the leading zeroes),
this happens:

0932-73 becomes 932.73, 1648-50 becomes 1648.5, and so on.

When I check the formatting of the cells, they're all still listed as
"Text", and this happens even if I specify that the number format of the
replacement should be text. What's the deal? Is there any way to keep this
from happening?