Using REPLACE command removes leading zeros

[Guy Kerr]

I have struggled with this for a long time and I don't know if it's a bug in
Excel (2003 and 2007) or if it's something I just need to learn how to do
correctly. The problem is I get a list of account #'s similar to the list
below.

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027

I have to remove the hypens in the account numbers. If I manually remove
the hypens by editing each record then everything works fine. If I use the
REPLACE command to replace - with nothing the accounts lose their leading 0.
I've tried setting the column formatting to TEXT or GENERAL before doing the
replace but get the same result.

This doesn't make sense to me. I would have thought if I set the format to
TEXT that it would retain the leading zero.

I now there are many other ways to do this but I really want to understand
if this is by design in Excel and I'm just not using the correct technique or
if this is a bug. If it is by design and someone can explain the design to
me that would be fantastic.

If anyone has any answers for me I'd be very much appreciative.

Guy

 

[Roger Govier]

Hi Guy

Try Substitute instead of Replace
=SUBSTITUTE(A1,"-","")

--
Regards
Roger Govier

I have struggled with this for a long time and I don't know if it's a bug
in
Excel (2003 and 2007) or if it's something I just need to learn how to do
correctly. The problem is I get a list of account #'s similar to the list
below.

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027

I have to remove the hypens in the account numbers. If I manually remove
the hypens by editing each record then everything works fine. If I use
the
REPLACE command to replace - with nothing the accounts lose their leading
0.
I've tried setting the column formatting to TEXT or GENERAL before doing
the
replace but get the same result.

This doesn't make sense to me. I would have thought if I set the format
to
TEXT that it would retain the leading zero.

I now there are many other ways to do this but I really want to understand
if this is by design in Excel and I'm just not using the correct technique
or
if this is a bug. If it is by design and someone can explain the design
to
me that would be fantastic.

If anyone has any answers for me I'd be very much appreciative.

Guy

__________ Information from ESET Smart Security, version of virus
signature database 4510 (20091015) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4510 (20091015) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Dave Peterson]

Since your entries may contain 16 digits, you won't be able to use a custom
number format--excel only keeps track of 15 digits.

Maybe you can just use a helper column and a formula:
=substitute(a1,"-","")
and drag down.
(Convert to values if you want.)