Originally published at: Dealing with Leading Zeroes in Spreadsheet Data - TidBITS
Adam and Tonya Engst discovered through an “undeliverable” holiday card that their approach to extracting data from Contacts to print labels inadvertently deleted leading zeroes in ZIP codes. Can they ever send snail mail to New England? Here’s what happened—and how to avoid similar errors.
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).
2 Likes
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:
When importing data into Excel, you can prevent ZIP Codes from being converted into four digits by formatting the column as a text column before importing. This will ensure that Excel will treat the ZIP Codes as text and not convert them into a numerical format.
3 Likes
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)…
- Hide Separator
- Show Zeros for Unused Digits.
- Then I select the little section, Copy it, press the space bar, then Paste, and then remove One Digit (the dropdown gives that option).
- Then copy the 3-digit integer, add a space, and paste the 3-digit iteger to give a 10-digit number with spaces in the correct spots.
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! 
4 Likes
There is the excellent Exporter for Contacts 2 im Mac App Store . Very useful if the conversion to csv has to be done regularly.
3 Likes
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.
1 Like
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.
1 Like
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
1 Like
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.
1 Like
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.
1 Like
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.
1 Like
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.
2 Likes
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.)
2 Likes
Yes, I got it wrong. Wrote too quickly and didn’t verify, apologies.
1 Like
Also in Excel you can do a custom number format of 00000 which will work exactly the same as the Zip code format
2 Likes