Leading Zero Dropped in Text Cell

[Edward DiMarco]

Long time reader, first time asker (read: I tried searching but could not
find the answer):
I have cells that have 4 digit numbers (e.g. 0034) and they need to be
formatted to the 00.00 format (end result: 00.34).

The issue is they were imported from an external data source, so all the
numbers are in Excel as text. When they are converted over to numbers, the
zeroes are dropped, so it ends up being 34. When I apply 00.00 formatting,
or =TEXT(A2,"00.00") or the other methods of keeping leading zeroes, the
number is 34.00, because it takes and tacks the number on the front.

Is there a way to convert these to numbers into the 00.00 format?

 

[Gary Keramidas]

just format the cell the way you want it to look. format cells, custom on the
number tab, enter 00.00 and apply it to the cells in question.

 

[Dave Peterson]

You could use a helper cell:
=a2/100
and give that helper cell/column a nice format (00.00)

or you could do the same thing using =text()
=text(a2/100,"00.00")

Or you could keep your data in the same location and divide by 100.

put 100 in an empty cell
edit|copy that cell
select the range to fix
edit|paste special|values and divide
and clean up the cell with 100 in it.

Then format the fixed range with a custom format of 00.00

 

[Edward DiMarco]

Dave, that's awesome. Thank you. Now I can have some semblence of sanity.