FORMATTING ZIP CODE COLUMN

jbyrne

Board Regular
Joined
Feb 22, 2002
Messages
178
I have a column of zip codes. Some have the zip +4 extentions and some don't. What I want to do is make them all just with the zip code and no 4 digit extention.

I also have a problem formatting the zip codes that begin with the number 0. It seems to cut the 0 off.

Any help on how I can accomplish this.

Thanks for any help
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
On 2002-05-03 20:16, jbyrne wrote:
I have a column of zip codes. Some have the zip +4 extentions and some don't. What I want to do is make them all just with the zip code and no 4 digit extention.

I also have a problem formatting the zip codes that begin with the number 0. It seems to cut the 0 off.

Any help on how I can accomplish this.

Thanks for any help

One, Format the ZipCodes as Text entries,
Two, to make entry in A1 a five digit entry, in an adjacent column, use =LEFT(A1,5)
 
Upvote 0
Hi Yogi,

My zip codes are in column F and in column G I am typing your formula =LEFT(F1,5)

I first format the zip codes as text. Then I enter in the formula in column G. I still seem to be missing the 0's in the zip codes that start with 0.

When I open the file, (csv file) I have quotes around the zipcode. I don't know why this is happening either.
 
Upvote 0
On 2002-05-03 20:37, jbyrne wrote:
Hi Yogi,

My zip codes are in column F and in column G I am typing your formula =LEFT(F1,5)

I first format the zip codes as text. Then I enter in the formula in column G. I still seem to be missing the 0's in the zip codes that start with 0.

When I open the file, (csv file) I have quotes around the zipcode. I don't know why this is happening either.

Hi jbyrne:
How about post some of the entries from column F and G ... and let us look at those.

Regards!

Yogi
 
Upvote 0
COL E F
"WI" "535115150"
"FL" "338094123"
"TN" "37087"
"KS" "666111468"
"KS" "66611"
"KS" "666111466"
"CA" "92692"
"KS" "666042625"
"MD" "217760744"
"MD" "21776"
"NJ" "080071049"
"NJ" "08108"
"NJ" "080432045"
"NJ" "080574224"

When I remove the quotes with the text to column method, the NJ zipcodes drop their 0's

Thanks Yogi for your help

Jim
 
Upvote 0
Hi
Once you get em down to five digits,
just select the cells in question, right-click, Choose format cells, choose the number tab, choose special, choose zipcode...
The zeros are still there, you just can't see 'em. With the above format, you get 5 numbers.. So if you type in 00005 you will see 00005...
I think that will help you as well...
Tom
This message was edited by TsTom on 2002-05-03 21:15
 
Upvote 0
Hi jim,

When the master of formulae "Aladin" reads your post you will be in good shape. This guy
is phenomenal, all business, no side jokes
and a true gentleman.

James
 
Upvote 0
On 2002-05-03 20:59, jbyrne wrote:
COL E F
"WI" "535115150"
"FL" "338094123"
"TN" "37087"
"KS" "666111468"
"KS" "66611"
"KS" "666111466"
"CA" "92692"
"KS" "666042625"
"MD" "217760744"
"MD" "21776"
"NJ" "080071049"
"NJ" "08108"
"NJ" "080432045"
"NJ" "080574224"

When I remove the quotes with the text to column method, the NJ zipcodes drop their 0's

Thanks Yogi for your help

Jim

Hi Jim use the following formula in cell G1 and then replicate down:

=LEFT(IF(OR(LEN(E1)=4,LEN(E1)=8),"0"&E1,E1),5)
what this formula does is check if a ZipCode is either 4 (short), or 8 (long), then it attaches a zero to the ZipCode entry in the first digit location.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!
 
Upvote 0
Hi Yogi,

Thanks for your reply and I'm sorry for the delay in responding. I've been on vacation. I will try your formula tonight and let you know how I did.
I know I had said the state was in col E and the zip was in F but I was mistaken.
My state is in column F and my zip is in column G. Can you let me know what I need to revise in your formula?

Thanks again
Jim
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top