Making 3 digit zip from 5 digit zip code

Pypcik27

Board Regular
Joined
Oct 23, 2006
Messages
78
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
 

Some videos you may like

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
Joined
Aug 26, 2006
Messages
82
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
Joined
Feb 25, 2002
Messages
4,053
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. o_O
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007

ADVERTISEMENT

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
Joined
Feb 25, 2002
Messages
4,053
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
Joined
Oct 23, 2006
Messages
78

ADVERTISEMENT

Thank you!!!

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

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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
Joined
Oct 23, 2006
Messages
78
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
Joined
Feb 25, 2002
Messages
4,053
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top