Howtos‎ > ‎

Excel Eats Zip Codes For Lunch

When I open a .csv or .txt from a thing like InfoSnap or PowerSchool, it displays Hanover zip code 03755 as "3755". I know how to fix it when it happens -- select the column, change the type to Special, Zip -- but it chafes me to have to do that every time I open/edit a file, when I'm working with InfoSnap tab- or comma-delimited exports that have five or six zip code columns each. So NOW, I am doing this:

1. Open the exported .txt in a plain text editor (BBEdit or TextWrangler on a Mac), select all, copy

2. Make a new Excel (I'm on Mac v. 2011) workbook, select all cells, make the Number type be "Text"

3. THEN put cursor in cell #1 and paste

And that seems to work -- I'm not doing any math with these values, so I don't care if every field is seen as text. As long as I remember to make the cell format be Text BEFORE I paste the values, the zip codes are preserved. 

But I'd like to get it down to fewer there a preference I could set in Excel semi-permanently, something along the lines of "treat all files as text by default unless I tell you otherwise" or "make the default number format be 'text' for all new worksheets" or something like that?  <- Posted to NHPSUG, will update this howto if I get a good answer.