FORMATTING ZIP CODE COLUMN

[jbyrne]

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

[Yogi Anand]

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)

[jbyrne]

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.

[Yogi Anand]

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

[jbyrne]

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

[Tom Schreiner]

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 ]

[James]

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

[jbyrne]

Tom,

Thanks, that worked great.

Thanks for your help!

Jim

[Yogi Anand]

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!

[jbyrne]

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