Cell Formatting to disp. ### numbers

[tusamni]

I am trying to format the cells so that it only allows three numbers t
be displayed.

To be more descriptive:

We work with zip codes quite often, but, we only use the first thre
digits.

Someone sent us a xls file with 12000 zip codes, in one column, and
need to know how to make the column show only the first three digits o
all the zip codes..

there is another problem, when I convert them to a numeric value, i
removes the zero in front...ex. 08245, becomes 8245, but i need to kee
that zero in front.

Thanks

-Eri

 

[CarlosAntenna]

Assuming your zip codes are in column A, you would put the following in
column B
=LEFT(A1,3)

 

[tusamni]

Thanks!

I can make that work...

what about the removal of the first zero when I convert the zip code
to a numeric value?

Thanks again,

Eri

 

[CarlosAntenna]

Format column B: format/cells/number/custom/000

You can do both at once by changing the formula to =value(left(a1,3))

 

[Dave Peterson]

Are they 5 digit codes that are entered as numbers (not text)?

=left(text(a1,"00000"),3)

so 8245 (= 08245) would return 082.