Find and Replace
 

Good day all,
I am trying to change this number 01-465-9273 to show up as 014659273. It is
easy enough to make the (-) go away but it takes the zeros at the beginning
of the number away as well which defeats the purpose

Thanks
--
Pierre

Find and Replace
 

A couple of alternatives:
- format the cell as Text before removing the dashes
- put an apostrophe before the 0 before removing the dashes
- remove the dashes in another cell, using SUBSTITUTE:
=SUBSTITUTE(A1,"-","")
- give the cell a custom number format like 000000000

Hope this helps,

Hutch

"Pierre" wrote:

Good day all,
I am trying to change this number 01-465-9273 to show up as 014659273. It is
easy enough to make the (-) go away but it takes the zeros at the beginning
of the number away as well which defeats the purpose

Thanks
--
Pierre

Find and Replace
 

Hi,

The first one will not work

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tom Hutchins" wrote in message
...
A couple of alternatives:
- format the cell as Text before removing the dashes
- put an apostrophe before the 0 before removing the dashes
- remove the dashes in another cell, using SUBSTITUTE:
=SUBSTITUTE(A1,"-","")
- give the cell a custom number format like 000000000

Hope this helps,

Hutch

"Pierre" wrote:

Good day all,
I am trying to change this number 01-465-9273 to show up as 014659273. It
is
easy enough to make the (-) go away but it takes the zeros at the
beginning
of the number away as well which defeats the purpose

Thanks
--
Pierre

Find and Replace
 

Thanks Hutch.
First one did not work
Second one, if I understood you correctly would require me to do this
humpteen times
Third, which seemed complicated to me, therefore I tried it last, turned out
to be really easy and worked perfectly
Forth and last one, worked for that cell, however when I tried to use that
cell in another formula, it dropped the zero(s)

Thank you very much
--
Pierre


"Tom Hutchins" wrote:

A couple of alternatives:
- format the cell as Text before removing the dashes
- put an apostrophe before the 0 before removing the dashes
- remove the dashes in another cell, using SUBSTITUTE:
=SUBSTITUTE(A1,"-","")
- give the cell a custom number format like 000000000

Hope this helps,

Hutch

"Pierre" wrote:

Good day all,
I am trying to change this number 01-465-9273 to show up as 014659273. It is
easy enough to make the (-) go away but it takes the zeros at the beginning
of the number away as well which defeats the purpose

Thanks
--
Pierre

Find and Replace
 

It worked when I tried it before posting the reply, and it works for me now
(Excel 2007). In a new workbook, in cells with a default format of General, I
enter the OP's sample data in two cells. If I just remove the dashes from
one, it becomes a number and drops the leading zero. If I format the second
cell as Text, then remove the dashes, it remains as text with the leading
zero intact. I get the same results with other similar leading-zero strings
of digits & dashes. 2007 vs. earlier version, maybe?

Thanks,

Hutch

"Ashish Mathur" wrote:

Hi,

The first one will not work

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tom Hutchins" wrote in message
...
A couple of alternatives:
- format the cell as Text before removing the dashes
- put an apostrophe before the 0 before removing the dashes
- remove the dashes in another cell, using SUBSTITUTE:
=SUBSTITUTE(A1,"-","")
- give the cell a custom number format like 000000000

Hope this helps,

Hutch

"Pierre" wrote:

Good day all,
I am trying to change this number 01-465-9273 to show up as 014659273. It
is
easy enough to make the (-) go away but it takes the zeros at the
beginning
of the number away as well which defeats the purpose

Thanks
--
Pierre