And piggybacking on John’s suggestion, you can also tell Excel to leave leading zeroes in imported data by prefixing the cell with an apostrophe (so '000501 gets parsed as 000501). This is handy if you only have a few lines; otherwise I use the custom formatting John mentioned to format the whole column to include leading zeroes.
1 Like
The leading apostrophe tells Excel that the cell is text, not a number. Which is, IMO, the right thing to do for postal codes.
1 Like
I learned a lot from this thread! But my response to those using Excel or OpenOffice etc would be to format the cells with leading zeros.
1 Like
Hi Adam,
I have a correction to the following statement:
“Changing the ZIP code column format back to Text after the fact has no effect because Excel parses the text as a number and converts the stored value into a number.”
It is correct that changing the format to text after the fact has no effect. After the data is in the cells as a number type, formatting the data does not change the data type of the data.
Perhaps that is what you mean, but “because Excel has already stored the values as numbers” would be better.
This seems pedantic, but the correct understanding of how spreadsheets store data vs. how they present data solves so many problems of “why isn’t this working?!”
Thanks,
Dave
1 Like
Absolutely! My first reaction. Have done this many times.
Dutch zip codes are formatted as 1234 AB
The space between the characters is prescribed, but if you leave it out there is no problem in real life.
Hi Dave, I too have printed my Christmas Card labels directly from Contacts for many years. It is by far the easiest, quickest and cheapest(!) solution. However, it is quite clunky and unintuitive until you get the hang of all the features. I wasted quite a few sheets of labels in the early days!
But your comment about having to print every address is incorrect. See my screen-grab attached.
You can choose only home or work etc or do what I did and create a Distribution List (the bottom option on the dropdown menu). Search for Distribution List in Contacts Help for how to do this.
1 Like
Excel also has a “Get Data” or “Power Query” button that works a bit differently - if you use that, make sure you choose “do not detect data types” while importing a CSV file:
And do that all the time. It’s not just Zip Codes that have the problem. I work with a lot of personal data, and I’ve seen Excel convert people’s usernames into dates (“mar20” becomes “March 20, 2023” for instance), and other semi-numeric data where I have never been able to comprehend why Excel has chosen to convert, irretrievably, to something else. Set all columns to text (or “do not detect”) and you can always convert to number later - but going the other way can be impossible.
1 Like
Perfectly true, but the problem is that most people are not programmers so Excel’s behavior is usually completely unexpected. Anything that causes data loss should require user confirmation, and Excel fails to do that (and if someone should unfortunately do a command-s before noticing, the loss becomes permanent - still no warning).
2 Likes
I haven’t dealt with any of that in a long time since I keep everything in Filemaker, but in the old days I would fix that problem in Excel by putting an apostrophe before the leading zeros. That always fooled Excel into thinking it was text.
No “fooling”. That’s the correct way to force any input string to be interpreted as literal text. Not just for numbers, but also text that resembles dates, times, currency, references or any other type that would otherwise be detected as something other than text.
Some examples:
'1234- Would otherwise be treated as a number'=A25- Would otherwise be treated as a cell reference'Dec-1- Would otherwise be treated as a date'10:53- Would otherwise be treated as time'$123.45- Would otherwise be treated as a number (with currency formatting)
Indeed. This phenomenon also has impacted scientific and academic research in multiple ways, from mis-recording ZIP codes to corrupting genetics data. The problem is serious enough that the official names of some genes have been changed to avoid problems with Excel, and guidelines for naming newly discovered genes specifically discuss avoiding names likely to encouner autocorrect/autoformat errors.
Further reading for those so inclined:
-
“Five years after a study showed that autocorrect problems were widespread, the academic literature is still littered with error-riddled spreadsheets, according to an analysis of published gene lists. And the problem may be even worse than previously realized.”
-
Excel Is to Blame For Major Typos in 20% of Scientific Papers on Genes
-
Here’s a paper on the subject that is nearly 20 years old: Gene name errors can be introduced inadvertently when using Excel in bioinformatics
-
“We surveyed more than 10,000 papers with Excel gene lists published between 2014 and 2020 and found more than 30% contained at least one gene name mangled by autocorrect.”
2 Likes
Interestingly, using the ZIP Code format that @aforkosh mentioned, or the 00000 custom format that others have suggested DOES change four-digit ZIP codes to have the leading zero. So it’s only changing it to Text that doesn’t.
But you’re correct about Excel already having stored the value as a number—that would be better wording.
I think I have seen a suggestion somewhere saying that you can put a period character immediately before ZIP codes with leading zeroes (like .00501 for Holtsville, NY) and that post offices would have no problem with that printed on mailing labels.
I don’t know whether (the current version of) Contacts app accepts that in the ZIP code field or not, though.
There is an excellent app “Address Labels and Envelopes” available on the App Store that will print envelopes and labels in addition to creating csv lists. Makes it simple to move data from contacts. No problem with zeros in zip codes.
I recently discovered Easy CSV Editor in the App Store. This very powerful tool makes it a breeze to handle this types of issues: simply select a column, enclose everything in that column in straight double quotes an prefix this with an =. This tool allows for all kinds of transformations of CSV files quickly and easily. I am so happy with this tool that I also bought the version for iOS/iPadOS.
1 Like
Interesting. At first glance, I don’t think Easy CSV Editor does anything that I don’t already do with a combination of BBEdit and Excel, but it looks very well designed.
I work with CSV files often enough that there may be some worthwhile efficiencies to be gained. Thanks for sharing!