Export drops the leading zero from zip code

Kelly Hatton

I just discovered that the export to Excel or CSV format drops the leading zeros from my zip codes.  Is there a quick fix in RE or export settings.  I know how to fix it in Excel but was hoping to fix the root issue for the benefit of all staff members doing mailing list exports.

Steven Cinquegrana 2

Hi Kelly,


I can't replicate this using csv format export if the result is opened in a text editor. But opening it in Excel will drop any leading zeros. Maybe that's the issue here?


In other situations, eg export from SQL, there are ways to "trick" Excel into formatting a field as Text instead of General, but I can't think of a way to do that in this situ.


Have you used NotePad++? It's a really good, free text editor which I'd recommend instead of using Excel, which is always trying to "help"* you.


Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions


* Try using Excel with raw credit card numbers if you really want to see how "helpful" Excel can be!

Kelly Hatton

Steven Cinquegrana:

Hi Kelly,


I can't replicate this using csv format export if the result is opened in a text editor. But opening it in Excel will drop any leading zeros. Maybe that's the issue here?


In other situations, eg export from SQL, there are ways to "trick" Excel into formatting a field as Text instead of General, but I can't think of a way to do that in this situ.


Have you used NotePad++? It's a really good, free text editor which I'd recommend instead of using Excel, which is always trying to "help"* you.


Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions


* Try using Excel with raw credit card numbers if you really want to see how "helpful" Excel can be!

Thanks for the reply!  Yes, the constituent IDs all export to excel as text but not the zipcodes only some of them export as text and some export as numbers! WEIRD.

I use Textpad editor so I am familiar with that, however, staff export mailing lists for the communications department to send to 3rd party mail houses and they want it in Excel format.  Well we don't have to worry about the credit card numbers because we don't store that information in our system. ;-)

Marie Stark

http://www.blackbaudknowhow.com/the-raisers-edge/where-are-my-leading-zeros.htm


Here is an old blog post that might help you.

 

 

Kelly Hatton

Marie Stark:

http://www.blackbaudknowhow.com/the-raisers-edge/where-are-my-leading-zeros.htm


Here is an old blog post that might help you.

 

 

 

THANK YOU, THANK YOU!!!  

Mary Kern 2

Excel assumes that the zip is a number except for the zip+4 which generally have a dash in them. If you change the name to .txt instead of .csv and go through the import wizard, you can tell Ecel that the column is either text or a zipcode in which case it won't drop the leading zeros.


Hope this helps!

Mary

roger berg

To fix what you've already exported in excel you can format the column with zipcodes as "Special" and within that choose zipcode and it will add the missing 0 at the beginning of the string. That doesn't solve the issue, but at least fixes it after the fact...

Barbara Schlichter

Kelly Hatton:

Steven Cinquegrana:

Hi Kelly,


I can't replicate this using csv format export if the result is opened in a text editor. But opening it in Excel will drop any leading zeros. Maybe that's the issue here?


In other situations, eg export from SQL, there are ways to "trick" Excel into formatting a field as Text instead of General, but I can't think of a way to do that in this situ.


Have you used NotePad++? It's a really good, free text editor which I'd recommend instead of using Excel, which is always trying to "help"* you.


Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions


* Try using Excel with raw credit card numbers if you really want to see how "helpful" Excel can be!

Thanks for the reply!  Yes, the constituent IDs all export to excel as text but not the zipcodes only some of them export as text and some export as numbers! WEIRD.

I use Textpad editor so I am familiar with that, however, staff export mailing lists for the communications department to send to 3rd party mail houses and they want it in Excel format.  Well we don't have to worry about the credit card numbers because we don't store that information in our system. ;-)

 

I would hope your third party mail house would fix that anomaly for you.  You can use =text(column#,"00000") to bring back the dropped zero.  Put that formula in an empty column, drag it to the bottom of the file.  Copy the whole column with the forumla.  Then go back up to where your original zipcode reside, right click and paste special, values.