Problems with number formats when using Find/Replace

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?