Many companies upload Excel spreadsheets full of contacts to their CRM

[Raihan8]

And it usually works. But there is one thing that Excel can have trouble with: ZIP codes that start with zero.

“According to 2016 census data, approximately 27 million people live in areas with ZIP codes beginning with zero, or 8% of the US population,” said Thomas Bonneau of gb|sterling . It’s a problem that Excel struggles with ZIP codes that start with zero.

"If you have a bad data file, you could be ignoring almost 10% of the national data set."

Even if you've dealt with this problem before, it can come up again, says Bonneau:

“If you have an Excel data file that contains postal codes and the thailand code number column is formatted correctly (special postal code or text), everything will look great if it has a code that starts with a zero (for example, 02739). However, if you save this file to CSV to import it into your CRM and then reopen it to edit it again, this leading zero will disappear because the CSV file ignores all the Excel formatting that caused the zero to be retained.”

So how do you fix this?

Fix the Excel file before uploading it to CRM:

Select the column with the zip code, go to Format Cells

On the Number tab, choose the Special Category

Select Zip Code and press OK

Now the entire row will add leading zeros to the zip codes that are missing

Make any other edits that are necessary, then save the file again as CSV

DO NOT re-open the file in Excel and import it directly into CRM. When you preview the data file in the CRM import tool, the leading zero should have been retained.

Stephanie Baiocchi from IMPACT has also encountered this problem. But she found an even easier way to solve it: “I’ve been using Insycle! I used to have to create a list in HubSpot and clean it up manually.”

And Baiocchi has seen an improvement. “We can send direct emails (even things like gifts to clients) without having to try every address or get undeliverable emails.”