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

 

[jbyrne]

Hi Yogi,

I successfully revised your formula to fit the right columns but when I save the file as a csv, it still opens without the leading zero on a 5 digit zipcode.

This is very confusing to me.

 

[jbyrne]

I'm stuck here at work on this problem.

If anyone has an idea on how I can do the following, I would greatly appreciate the help!

I have a file that I need to save as a csv file before importing the data into my computer system. The problem I'm having is that the zip codes that start with a zero, only have a 4 digit zip. (not showing the leading zero). Same case for SS#'s. Any social starting with a zero, is being left off and I'm stuck with a 8 digit social.

My social column is in column D
My Zipcode column is in column I

I've tried to save it as text first, than csv, but when I open the csv file. The leading zero's are not there.

End result, I need the leading zero's to stay there even when I save the file as a csv.

Any help on this??

Thanks
Jim

 

[walt1122]

Cause I am not to good at code, try this: high-lite column, then go to "data" choose "text to columns" and just go through the steps involved ( shouldn't need to change any of the settings). When done, and columns are still high-lited, just go to format, say special, and zip codes. this should fill in the number whatever it is to 5 digits ( 5 becomes 00005). If this doesn't work I remember I had another way to have the system do it for me. I have it on some spreadsheet somewhere. I stole bits and pieces from others until I got it to work. Let me know and I will take a look for it.

Walt