Dealing with Leading Zeroes in Spreadsheet Data
Call us old-fashioned, but Tonya and I still enjoy sending physical holiday cards to summarize the past year for family and friends. We’re not always on target with completing the task in December, and there have been several years when we delayed enough that our New Year’s roundup appeared in the guise of Valentine’s Day cards sent in mid-February. Amusingly, getting our cards out late has often been rewarded with more conversations with friends because Valentine’s Day cards stand out from those that arrive around Christmas or New Year’s.
Why am I writing about holiday cards now, in mid-March? Because two months after we sent out our batch, one of them just bounced back. Although we were surprised it took so long, we were more perturbed that we had gotten the address wrong. It took a few minutes of staring at the card before Tonya realized the problem. And then a light dawned.
Most Northeast states have ZIP codes that start with zeroes: Connecticut, Maine, Massachusetts, New Hampshire, New Jersey, New York, Rhode Island, and Vermont, not to mention Puerto Rico and the special delivery military/overseas APO/FPO European destinations. After some marital disagreement about our label-making process this year, Tonya took over the job and chose to switch from my approach—connecting Contacts to Belight Software’s Swift Publisher—to the Avery Design & Print Online tool. To move addresses from Contacts into Avery’s Web app, Tonya needed them in Excel (XLS or XLSX) or CSV (text-only comma-separated values) formats. Unfortunately, Contacts can’t export to CSV, just VCF (also known as vCard) and its own ABBU format (which it calls a Contacts archive).
Tonya transferred the data out of Contacts by selecting all the cards in her Holiday Cards group and dragging the selection into an empty Numbers sheet. She couldn’t figure out how to transform the data the way she wanted there, so she copied it into Microsoft Word. After several years of supporting Word at Microsoft in the early 1990s, it’s her go-to tool for just about everything. I can’t criticize; my default hammer is BBEdit and grep.
The problem likely arose due to her moving it from Word into Excel so she could export a file that Avery would accept. By default, Excel formats columns of pasted or imported data as General, a numeric format. Leading zeroes don’t make sense in numeric formats—007 is the same as 7, Mr. Bond—and Excel blithely discards them, turning a five-digit ZIP code into an incorrect one with only four digits. (ZIP codes can even be three digits: sorry, Holtsville, NY 00501!) 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. Apple’s Numbers treats most data—other than that copied or dragged from Contacts—the same way. (Thanks to Alan Forkosh for alerting us after initial publication that Excel actually has a ZIP Code format buried in Format > Cells > Number > Special. Changing to that format would have solved the problem.)
Since we knew we had entered the ZIP codes correctly in Contacts and haven’t memorized our friends’ ZIP codes, we missed the four-digit ZIPs while proofing the labels—who looks carefully at a ZIP code? Worse, we didn’t even figure it out for the first two cards that bounced back—sorry, Paul and Andy! (Interestingly, not all of our four-digit ZIP cards have bounced yet, which could suggest additional delays or that some post offices or mail scanners worked around our mistake. Printed and most handwritten addresses are managed entirely automatically.)
So, if you’re ever putting US addresses—or other data with leading zeroes, like UPC or SKU numbers—into a spreadsheet, it’s worth making sure that no leading zeroes have been dropped. I did some experimentation and found two approaches that preserve a ZIP code’s leading zeroes when moving the data out of Contacts. You’ll also find tips for dealing with other types of data.
Contacts > Numbers > CSV
When you stay within the Apple ecosystem, everything just works as long as you know that dragging or copying and pasting data from Contacts into Numbers brings over everything, complete with leading zeroes in ZIP codes. So:
- Select the desired people in Contacts. This is most easily done if they’re in a group, at which point Command-A is your friend.
- Press Command-C to copy them.
- Switch to Numbers, create a new blank spreadsheet, and paste with Command-V.
In other words, it’s just a couple of clicks and keystrokes. Then you can remove unnecessary columns, munge the data, and export to CSV.
Things become more complex if you want to bring data with leading zeroes into Numbers from some other source. In that case, the solution seems to be to enclose the data in double quotes and prefix it with an equal sign. Thus, if you had a ZIP code of 07470, you’d need to change it in the source data to ="07470" before opening it in Numbers. (Previous advice to enclose numbers in single quotes no longer seems to work.)
I leave the exercise of editing an entire column of such ZIP codes in a CSV file to the reader, but it’s a breeze in a grep-capable editor like BBEdit or Nisus Writer Pro: search for ,([0-9\-]{5,10}), and replace with ,="\1",.
Contacts > VCF > CSV > Excel > CSV
Any Mac user should be able to copy from Contacts and paste into Numbers—it’s free to download from the Mac App Store. For the purposes of argument, however, let’s say that you need to go from Contacts to Excel without involving Numbers. Happily, converting an exported VCF file into CSV is easy.
- In Contacts, select the desired people.
- Choose File > Export > Export vCard and save the file somewhere convenient.
- Navigate to the vCard to LDIF/CSV Converter website, click Choose File, and select your file.
- Choose CSV from the Format menu, and click Convert. The converted file downloads automatically.
Once you have a CSV file of addresses or some other data to import, follow these steps to open it in Excel while retaining leading zeroes:
- In Excel, create a new file.
- Choose File > Import.
- In the Import dialog, select CSV file and click Import.
- Select your file in the Open dialog that appears next.
- In Step 1 of the Text Import Wizard, make sure Delimited is selected and click Next.
- In Step 2 of the wizard, deselect Tab and select Comma. Notice how your data lines up neatly in columns once you do. Click Next.
- Step 3 of the wizard is the key. While the first column remains selected (black), swipe (with two fingers) or scroll horizontally to the rightmost column (with a scroll wheel, hold down the Shift key to scroll horizontally), and then Shift-click that column to select all the columns in between, turning them all black.
- Click the Text radio button and notice how the column headers change from General to Text. Click Finish.
- In the final Import Data dialog, select the destination for the data and click Import.
If you plan to work with this file in the future, save it in XLSX format rather than CSV. CSV is a text format and doesn’t retain formatting, which means you would lose the leading zeroes if you imported it into Excel again without using the Text Import Wizard.
Note that the trick of enclosing data with leading zeroes in double quotes and prefixing it with an equal sign also works in Excel.
It’s embarrassing that, after 35 years for Excel and 16 for Numbers, neither handles imported leading zeroes without significant extra work. How hard would it be to notice that a file contains leading zeroes and ask the user whether or not to retain them? Even Google Sheets provides a simple checkbox for this in its Import dialog—deselect it to import ZIP codes perfectly.
Of course, none of this would have been necessary if Contacts had halfway decent label-printing capabilities. We only want two Miss Manners-approved label-formatting options to ensure that our cards are appropriately addressed to couples:
- For couples with the same last name, the first line of the label would be
First & Spouse. For instance, that would pullAdamfrom my contact card’s first name field and& Tonya Engstfrom its Spouse field to createAdam & Tonya Engst. - For couples with different last names, the first line would use
First Last & Spouseinstead. If Tonya were still using her maiden name, the result would beAdam Engst & Tonya Byard.
In both cases, we ensure the Spouse field contains the spouse’s full name.
Swift Publisher can do this, but its connection with Contacts can be tenuous. I’m unaware of any other apps that offer such formatting options with data from Contacts, but if you know of any, please let us know in the comments!
There is a much easier solution in Excel: Change the format of the cells in the zip code column from Number to Zip Code (found under the category Special).
In computer programming, we have a saying: If you can’t perform a mathematical computation on it, it’s not a number.
This includes all sorts of number formats like zip codes, phone numbers, street addresses, etc.
By the way, I asked Petey an Apple Watch app (Formally known as WatchGPT) that lets you use ChatGPT to answer queries.
Here’s its answer:
This is not directly related to Adam’s posting, but may benefit other Numbers users.
I use Numbers quite a lot for large spreadsheets of players involved in sporting clubs and hence need mobile phone numbers to start with a Zero. (In Australia every 10-digit mobile (cell) phone number starts with 04. (eg. 0418 123 123) with a space after the first four digits, and another space after the next three digits, which is the common way mobile numbers are displayed in Australia.
And, as I usually gather information from club members via a GoogleForm (or similar database form) the way people enter their mobile number varies, some with spaces, some without, and some with the international code +61 (0) 418 123 123.
Whenever they enter a number with a leading zero (without any spaces) that zero drops out in the form collecting the data.
And as I use Numbers for manipulating the data (I’m a huge fan of Organise → Categories feature for focussed displaying, as well as pivot tables and other sorting options Numbers has), I discovered that in the CELL → Data Format drop down (in the toolbar) there is a Create Custom Format… option.
I create one called Mobile and adjust the settings so that cells automatically display the entries as mobile numbers with a leading zero and spaces in the correct position.
The Integer: options I choose to change are to (after clicking on the little down arrow)…
The final result looks like this…
It probably could be used for ZIP codes as well?
Hopefully that info is assistance to someone.
Oh, just “one more thing!” (as some famous person was renowned for saying) … Numbers could be set up to print onto Labels (with cells the size of the label? Or import the data into Pages, with text flowing from one text box to another (which are the size of the labels). Plenty of ways to skin a cat!
There is the excellent Exporter for Contacts 2 im Mac App Store . Very useful if the conversion to csv has to be done regularly.
I was going to post the same recommendation @os1: Exporter for Contacts 2 is excellent and would make exporting the contacts you want to the CSV format you want (e.g. only specific contact fields) trivial. You can save multiple export settings. And you could also export directly to Excel if that’s your ultimate goal, it supports several export options:
I use Exporter for Contacts every month to backup my contacts to vCards. It’s well worth the purchase price.
Just want to also recommend Exporter for Contacts 2. I’ve used it for years to put addresses into CSV format to make labels in Word for my holiday cards. It works and is very easy to setup.
I agree completely. A zip code is not a number. Format it as text and import the field as text.
It may have more than digits. For example, the hyphen separating the first five digits from the four-digit suffix.
And in other countries, they may be alphanumeric codes.
I print from Contacts. For anyone who’s in a household, I have a separate entry for the household and the individual, with a standardized note in the individual record indicating that a household record exists. If I am organized, I can keep shared phone numbers/emails in the household record and mobile phone numbers/emails in the individual record, so that my caller ID and mail labels are correct. And I can tweak the name format in the family record as appropriate – so I can make it go to “The Smith-Jones Family” when my friends have kids, or “Sue Smith and Steve Jones” when it’s just the two of them.
(One annoying limitation is that if you’re printing address labels, Contacts prints a label for every address on the selected record. So when I do Christmas card labels, I start by doing a print to PDF, and then edit the PDF in Preview to mark out or blank out the addresses I don’t want. That’s easier than reviewing the labels manually when I’m actually handling the envelopes.)
Dave
Living in Massachusetts, I am familiar with this problem. Once in a while we do receive mail with a zip code missing the initial 0 and containing only the four digits following it. But that isn’t very often, and although I haven’t checked, I suspect that might happen if the postal sorting bar code does include the bars representing the lead zero.
I used Excel for family contacts. For a Massachusetts zip code, I just put a period in front of the zero. So .0xxxx. It worked for me.
Who knew? (Apparently you and not me.
)
I’m skeptical that this would be easy, but I’m all ears if you think there’s a workflow here.
I used to think I was somewhat competent with grep, but I do not understand
{5,10}in the expression,([0-9\-]{5,10}),. Would you educate me, please?And thank you for the nudge to find that Contacts offers many more fields than I have been using, such a maiden name and nickname. But I cannot find spouse in the list of available fields. Did you create that somehow? Or are you using a newer version of Contacts? Mine is “Version 13.0 (2452.7.1)” and I’m running macOS 11.7.4.
The
{5,10}means 5x or 10x the previous character set. So in all, it’s saying ‘5 or 10 characters, each of which is a digit (zero through nine) or a hyphen’.The field you’re looking for is
Related Name. From there you can choose from several labels such as ‘spouse’, ‘child’, ‘daughter’, etc. You can also enter a custom one. And you can have multiple related names on a single contact.It actually means 5 through 10 (inclusive) of the expression it follows. So in this case, 5, 6, 7, 8, 9, or 10 digits or hyphens would match, but 4 or less or 11 or more wouldn’t.
The workflow is more suited to Pages (using linked text boxes, in Layout Mode) than Numbers.
See example screenshot of a Pages document.
The Pages workflow requires the raw data to be available as one long “galley” of text, and ideally the same number of lines per address, and then format the text with a “Style” to fit the label size.
Dedicated typesetting programs like Affinity Publisher can “force” text into new text boxes (or columns etc) with a keyboard character, instead of manually adding extra “returns” or “soft returns” as is required if using Pages.
For those few occasions where I’ve needed to print labels, I did it with FileMaker. Put all the addresses in database records. Create a layout that aligns with the labels I’m printing onto. And print.
Although I haven’t tried it, I would assume that it would be just as simple using a free database package like Libre Office Base.
Thank you. I have started to move information from comments in the Contacts’ records to the appropriate fields.
Thank you both. (In my rudimentary testing, @blm is correct.)
Yes, I got it wrong. Wrote too quickly and didn’t verify, apologies.
Also in Excel you can do a custom number format of 00000 which will work exactly the same as the Zip code format
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.
The leading apostrophe tells Excel that the cell is text, not a number. Which is, IMO, the right thing to do for postal codes.
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.
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
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.
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.
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).
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.”
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.
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!