The Numbers import feature for iOS, iPadOS, and macOS can take data in common comma-separated value (CSV) and tab-separated value (TSV) formats and parse it directly into fields on a sheet Calculation. However, you may have encountered a problem if you import data exported elsewhere or formatted by hand that should not be interpreted as a number but rather as plain text.
For example, the United States Postal Service’s ZIP code system is one of the most common uses of leading or padding zeros in the numbers you deal with every day. In a zip code like 04843, the leading zero is a necessary part of the zip code, but if you import a CSV or TSV file with 04843 in a field alone, Numbers (like Excel) automatically treats it as a number. The column automatically applies numeric formatting and removes that zero when converting to a pure number: 4843.
The error is also silent. If you’re not paying attention, the first time you know something is wrong is when the post office rejects an address or you experience similar validation issues with less common numbers with zeros in them. non-significant or other formatting that triggers numbers interpreting the field as a number.
There is an easy way around this problem, which Excel users have used for years, but it is oddly not a standard option in any tool I have used that exports CSV or TSV files. In the CSV or TSV file, instead of formatting a number by itself like ,04843, Or <tab>04843<tab> you add an equals sign and enclose the number in quotation marks, like:
="04843"
Numbers (and Excel) reads this as plain text. After importing, you can select the column and explicitly set it to text via the Format Inspector > Cell > Data formatwhere you choose Text.
Now, what if you can’t control how your CSV or TSV is created? Add a processing step before opening or importing the file into Numbers. In an app like BBEdit (including its free version), use Find and Replace. Here are the instructions for BBEdit after making a backup copy of your file:
- Open the CSV or TSV file in BBEdit.
- Choose Search > Search or press Command-F.
- Make sure the check boxes in the Find dialog box are set as follows: “Case sensitive” and “Whole word” are deselected and “Grep” and “Wrap around” are selected.
- In the Search for CSV file field, enter
,(\d+?),; for TSV,\t(\d+?)\t(I will repeat them below for clarity). - In the Replace CSV file field, enter
,="\1",; for TSV,\t="\1"\t. (Same.) - Click on Replace everything.
- Choose File > Save Or File > Save As.
- Open the CSV or TSV file in Numbers. Note the leading zeros!
BBEdit (shown here with a TSV replacement) and other text editing programs can allow you to easily repair a CSV or TSV file to prevent loss of leading zeros.
Foundry
These patterns above say “search for any number between commas (CSV) or tabs (CSV) and replace the number with =”number”. Although this forces all imported numbers to be treated as tet, you can use the Format Inspector to change the cell formatting of any column for which you want the numbers to act on the values in those columns as than numbers.
For clarity for steps 4 and 5, here is what you copy and paste into each field for CSV:
- Search in CSV:
,(\d+?), - Replace in CSV:
,="\1",
And for the TSV:
- Search in TSV:
\t(\d+?)\t - Replace in TSV:
\t="\1"\t
Note that these are straight quotes in the replacement templates for CSV and TSV, not curly quotes or typography.
Ask Mac 911
We’ve compiled a list of the questions we get asked most frequently, along with answers and links to columns: read our super FAQ to see if your question is covered. Otherwise, we are always looking for new problems to solve! Send yours to [email protected], including screenshots where applicable and if you would like your full name used. We will not answer all questions, we do not respond to emails, and we cannot provide direct troubleshooting advice.
