# Making 3 digit zip from 5 digit zip code

#### Pypcik27

##### Board Regular
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### xapie128

##### Board Regular
Assuming the zip code is in A1
Code:
``=LEFT(A1,3)``
(or use RIGHT if you want the right most 3 digits)

~

L

#### Legacy 68403

##### Guest
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

##### Well-known Member
Did I miss any possible combination?
Book1
ABCD
112345123=LEFT(A1,3)
212345124=LEFT(A2,2)&MID(A2,4,1)
312345125=LEFT(A3,2)&RIGHT(A3,1)
412345134=LEFT(A4,1)&MID(A4,3,2)
512345135=LEFT(A5,1)&MID(A5,3,1)&RIGHT(A5,1)
612345234=MID(A6,2,3)
712345235=MID(A7,2,2)&RIGHT(A7,1)
812345245=MID(A8,2,1)&RIGHT(A8,2)
912345345=RIGHT(A9,3)
Sheet1

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

#### tusharm

##### MrExcel MVP

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

##### Well-known Member
Tushar, your formula seems to work even if the numbers are stored as text.
Book1
ABCD
1number12345123=INT(A1/100)
2text12345123=INT(A2/100)
3246=SUM(B1:B2)
4
5number1234512345=INT(A5)
6text1234512345=INT(A6)
724690=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

##### Board Regular

Thank you!!!

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

#### PA HS Teacher

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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
ABCD
1number2345023=TEXT(INT(B1/100),"000")
2text02345023=TEXT(INT(B2/100),"000")
30=SUM(C1:C2)
4
5number2345023=TEXT(INT(B5/100),"000")
6text02345023=TEXT(INT(B6/100),"000")
70=SUM(C5:C6)
Sheet2

Replies
14
Views
132
Replies
4
Views
51
Replies
27
Views
848
Replies
10
Views
92
Replies
5
Views
87