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

  • Thread starter Thread starter zach
  • Start date Start date
Z

zach

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,"-","")
 
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
 
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)
 
Back
Top