Reprinted from TidBITS by permission; reuse governed by Creative Commons license BY-NC-ND 3.0. TidBITS has offered years of thoughtful commentary on Apple and Internet topics. For free email subscriptions and access to the entire TidBITS archive, visit http://www.tidbits.com/ Dealing with Leading Zeroes in Spreadsheet Data Adam Engst 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 was often rewarded with 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. Several 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 [1]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 [2]Swift Publisher'to instead using the online [3]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 was unable to figure out how to transform it the way she wanted to 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 that she could upload the data to Avery. By default, Excel formats columns of pasted or imported data as General, a numeric format. Leading zeroes don't make sense in numeric formats'[4]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. (It 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 can treat data the same way, though not when you drag-and-drop in to an empty sheet from Contacts. 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. (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 one key fact: copying and pasting data from Contacts into Numbers brings over all the data, complete with leading zeroes in ZIP codes. So: 1. 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. 2. Press Command-C to copy them. 3. Switch to Numbers, create a new blank spreadsheet, and paste with Command-V. In other words, it's merely 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, [5]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 edit 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. However, for the purposes of argument, 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. 1. In Contacts, select the desired people. 2. Choose File > Export > Export vCard and save the file on your Desktop. 3. Navigate to the [6]vCard to LDIF/CSV Converter website, click Choose File, and select your file. 4. Choose CSV from the Format menu, and click Convert. The converted file downloads automatically. Once you have a CSV file, follow these steps if you want to open it in Excel to remove columns or make other changes: 1. In Excel, create a new file. 2. Choose File > Import. 3. In the Import dialog, select CSV file and click Import. 4. Select your file in the Open dialog that appears next. 5. In Step 1 of the Text Import Wizard, make sure Delimited is selected and click Next. 6. In Step 2, deselect Tab and select Comma. Notice how your data lines up neatly in columns once you do. Click Next. 7. Step 3 is the key. While the first column remains selected (black), swipe (with two fingers) or scroll horizontally to the rightmost column (hold the Shift with a scroll wheel), and then Shift-click on that column to select all the columns in between. 8. Click the Text radio button and notice how the column headers change from General to Text. Click Finish. 9. 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 file and doesn't retain formatting, which means you'd lose the leading zeroes if you imported it into Excel again without using 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. 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 pull Adam from my contact card's first name field and & Tonya Engst from its Spouse field to create Adam & Tonya Engst. * For couples with different last names, the first line would use First Last & Spouse instead. If Tonya were still using her maiden name, the result would be Adam 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! References Visible links 1. https://pe.usps.com/text/pub28/28c2_010.htm 2. https://www.swiftpublisher.com/ 3. https://www.avery.com/software/design-and-print/ 4. https://en.wikipedia.org/wiki/00_Agent 5. https://apple.stackexchange.com/a/353561/15074 6. https://labs.brotherli.ch/vcfconvert/ Hidden links: 7. https://tidbits.com/wp/../uploads/2023/03/VCF-CSV-converter.png 8. https://tidbits.com/wp/../uploads/2023/03/Excel-import-1.png 9. https://tidbits.com/wp/../uploads/2023/03/Excel-import-2.png 10. https://tidbits.com/wp/../uploads/2023/03/Excel-import-3.png 11. https://tidbits.com/wp/../uploads/2023/03/Excel-import-4.png 12. https://tidbits.com/wp/../uploads/2023/03/Excel-import-5.png .