Making 3 digit zip from 5 digit zip code

[Pypcik27]

Does anyone know how to remove numbers in excel, I need to to remove 2 last digits from a 5 digit zip code making it into 3 digit zip code.

Thank you so much

 

[xapie128]

Assuming the zip code is in A1

=LEFT(A1,3)

(or use RIGHT if you want the right most 3 digits)

~

 

[Legacy 68403]

You could use the left function =left(A1,3) and scroll down the list of numbers. Then copy and paste special as values once this is done you could delete off the list of numbers.

 

[Barry Katcher]

Did I miss any possible combination?

Book1
	A	B	C	D
1	12345	123	=LEFT(A1,3)
2	12345	124	=LEFT(A2,2)&MID(A2,4,1)
3	12345	125	=LEFT(A3,2)&RIGHT(A3,1)
4	12345	134	=LEFT(A4,1)&MID(A4,3,2)
5	12345	135	=LEFT(A5,1)&MID(A5,3,1)&RIGHT(A5,1)
6	12345	234	=MID(A6,2,3)
7	12345	235	=MID(A7,2,2)&RIGHT(A7,1)
8	12345	245	=MID(A8,2,1)&RIGHT(A8,2)
9	12345	345	=RIGHT(A9,3)
Sheet1

Betcha' can't tell how bored out of my skull I must be.

 

[tusharm]

If the zip codes are stored as numbers, use =INT(x/100) where x is the cell containing the full zip code.

Does anyone know how to remove numbers in excel, I need to to remove 2 last digits from a 5 digit zip code making it into 3 digit zip code.

Thank you so much

 

[Barry Katcher]

Tushar, your formula seems to work even if the numbers are stored as text.

Book1
	A	B	C	D
1	number	12345	123	=INT(A1/100)
2	text	12345	123	=INT(A2/100)
3			246	=SUM(B1:B2)
4
5	number	12345	12345	=INT(A5)
6	text	12345	12345	=INT(A6)
7			24690	=SUM(B5:B6)
Sheet1

Apparently, the INT function forces the text numbers over to true numbers, similar to *1 or +0 or the VALUE function. Interesting.

 

[Pypcik27]

Thank you!!!

Everyone thank you so much, this is perfect, it Works!!!

 

[PA HS Teacher]

Tushar, your formula seems to work even if the numbers are stored as text.

I believe the operation of division coerces a number anyway, but I had never thought about using Int() for that purpost either.

 

[Pypcik27]

Last quick question

When I use any of the formulas it works but I lose 0, for example if I have a zip code that starts with 06003--- I only get "60" not "060".

Thanks you very much!

 

[Barry Katcher]

O.K., try this. You'll note that the formulas return text, and that you can't do any calculations on them as they are (note the SUM formulas that are zero). But you most likely won't be doing calculations on zip codes, anyway.

Book1
	A	B	C	D
1	number	2345	023	=TEXT(INT(B1/100),"000")
2	text	02345	023	=TEXT(INT(B2/100),"000")
3			0	=SUM(C1:C2)
4
5	number	2345	023	=TEXT(INT(B5/100),"000")
6	text	02345	023	=TEXT(INT(B6/100),"000")
7			0	=SUM(C5:C6)
Sheet2