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

--
Message posted from http://www.ExcelForum.com

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

"tusamni >" <<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I am trying to format the cells so that it only allows three numbers to
> be displayed.
>
> To be more descriptive:
>
> We work with zip codes quite often, but, we only use the first three
> digits.
>
> Someone sent us a xls file with 12000 zip codes, in one column, and I
> need to know how to make the column show only the first three digits of
> all the zip codes..
>
> there is another problem, when I convert them to a numeric value, it
> removes the zero in front...ex. 08245, becomes 8245, but i need to keep
> that zero in front.
>
> Thanks
>
> -Erik
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

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

--
Message posted from http://www.ExcelForum.com

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

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


"tusamni >" <<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks!
>
> I can make that work...
>
> what about the removal of the first zero when I convert the zip codes
> to a numeric value?
>
> Thanks again,
>
> Erik
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

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

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

so 8245 (= 08245) would return 082.





"tusamni <" wrote:
>
> I am trying to format the cells so that it only allows three numbers to
> be displayed.
>
> To be more descriptive:
>
> We work with zip codes quite often, but, we only use the first three
> digits.
>
> Someone sent us a xls file with 12000 zip codes, in one column, and I
> need to know how to make the column show only the first three digits of
> all the zip codes..
>
> there is another problem, when I convert them to a numeric value, it
> removes the zero in front...ex. 08245, becomes 8245, but i need to keep
> that zero in front.
>
> Thanks
>
> -Erik
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
(E-Mail Removed)