Need cell to be formatted as a number with a leading zero

I have a large string of values, i.e. 01818, 01812, etc.
that are formatted as text. I need these exact numbers
to be formatted as numbers without dropping the leading
zero. I also have a string of numbers that are formatted
as 012-985-000 where I must remove all dashes without
dropping the leading zero. Please Help...thanks in
advance

zach

When you say "without dropping the leading zero" - are you referring to what
you see on the screen? Note you can still do math on text '01812. If you
truly convert to a number, the 0 will be dropped, however you can still
format it to appear with a 0 before it.
To convert them to numbers, enter 1 in a blank cell, select the text cells,
edit paste special multiply OK.
Then format them as custom, something like

0##########



As for removing the -, try
=SUBSTITUTE(A1,"-","")


"zach" wrote in message
...
I have a large string of values, i.e. 01818, 01812, etc.
that are formatted as text. I need these exact numbers
to be formatted as numbers without dropping the leading
zero. I also have a string of numbers that are formatted
as 012-985-000 where I must remove all dashes without
dropping the leading zero. Please Help...thanks in
advance

zach

No, I need to do match and lookup functions on these
numbers and when they are formatted as text I am unable
to match items on different sheets even though they
appear to be identical...so i guess i am asking if there
is any way to have a cell formatted as number with a
leading zero? and if not, how can i do a match function
on cells that have a leading zero?
zach
-----Original Message-----
When you say "without dropping the leading zero" - are
you referring to what
you see on the screen? Note you can still do math on
text '01812. If you
truly convert to a number, the 0 will be dropped,
however you can still
format it to appear with a 0 before it.
To convert them to numbers, enter 1 in a blank cell,
select the text cells,
edit paste special multiply OK.
Then format them as custom, something like

0##########



As for removing the -, try
=SUBSTITUTE(A1,"-","")


"zach" wrote in
message
...
I have a large string of values, i.e. 01818, 01812,
etc.
that are formatted as text. I need these exact numbers
to be formatted as numbers without dropping the leading
zero. I also have a string of numbers that are
formatted
as 012-985-000 where I must remove all dashes without
dropping the leading zero. Please Help...thanks in
advance

zach


.

OK if that's the case all you have to do is multiply by -- to turn the text
into a number that can be matched with other numbers.

e.g. if A1 contains '01832

and you have a table of NUMBERS, including 1832 in B1:B10 you can use

=MATCH(--A1,B1:B10,0)


"zach" wrote in message
...
No, I need to do match and lookup functions on these
numbers and when they are formatted as text I am unable
to match items on different sheets even though they
appear to be identical...so i guess i am asking if there
is any way to have a cell formatted as number with a
leading zero? and if not, how can i do a match function
on cells that have a leading zero?
zach
-----Original Message-----
When you say "without dropping the leading zero" - are
you referring to what
you see on the screen? Note you can still do math on
text '01812. If you
truly convert to a number, the 0 will be dropped,
however you can still
format it to appear with a 0 before it.
To convert them to numbers, enter 1 in a blank cell,
select the text cells,
edit paste special multiply OK.
Then format them as custom, something like

0##########



As for removing the -, try
=SUBSTITUTE(A1,"-","")


"zach" wrote in
message
...
I have a large string of values, i.e. 01818, 01812,
etc.
that are formatted as text. I need these exact numbers
to be formatted as numbers without dropping the leading
zero. I also have a string of numbers that are
formatted
as 012-985-000 where I must remove all dashes without
dropping the leading zero. Please Help...thanks in
advance

zach


.