The import feature in Numbers for iOS, iPadOS, and macOS can take data in the common comma-separated values (CSV) and tab-separated values (TSV) formats and parse that right into a spreadsheet’s fields. However, you may have encountered a problem if you bring in data that was exported from elsewhere or formatted by hand that shouldn’t be interpreted as a number but, rather, as plain text.
For instance, the United States Postal Service Zip code system is one of the most common uses of leading or padding zeroes in numbers you deal with every day. In a zip code like 04843, the initial zero is a necessary part of the Zip code—but if you import a CSV or TSV file with 04843 in a field by itself, Numbers (like Excel) automatically treats it like a number. The column applies numeric formatting automatically and trims off that zero when converting it to a pure number: 4843.
The error is silent, too. If you’re not paying attention, the first time you know something is wrong is when the post office rejects an address, or you encounter similar validation problems with less commonly occurring numbers that have leading zeroes or other formatting that triggers Numbers interpreting the field as a number.
There’s an easy way to work around this, one that people using Excel have used for years, but it’s oddly not a standard option in any tool I’ve 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 straight text. After importing, you can select the column and explicitly set it to text via the Format Inspector > Cell > Data Format, where you choose Text.
Now, what if you can’t control how your CSV or TSV is made? Add a processing step before you open or import 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 > Find or press Command-F.
- Make sure the checkboxes in the Find dialog are set as follows: “Case sensitive” and “Entire word” are deselected and “Grep” and “Wrap around” are selected.
- In the Find field for a CSV file, enter
,(d+?),; for TSV,t(d+?)t(I’ll repeat these below for clarity). - In the Replace field for a CSV file, enter
,="1",; for TSV,t="1"t. (Ditto.) - Click Replace All.
- Choose File > Save or File > Save As.
- Open the CSV or TSV file in Numbers. Note the leading zeroes!
BBEdit (shown here with a TSV replacement) and other text-editing programs can let you easily fix a CSV or TSV file to prevent the loss of leading zeroes.
Foundry
Those patterns above say “find any number between commas (CSV) or tabs (CSV) and replace the number with =”number”. While this forces all imported numbers to be treated like tet, you can use the Format Inspector to change the cell formatting for any column you like to have Numbers act on those columns’ values as numbers.
For additional clarity for step 4 and 5, here’s what you copy and paste in each field for CSV:
- Find in CSV:
,(d+?), - Replace in CSV:
,="1",
And for TSV:
- Find in TSV:
t(d+?)t - Replace in TSV:
t="1"t
Note that those are straight quotation marks in the replacement patterns for CSV and TSV, not curly or typographers’ quotes.
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. If not, we’re always looking for new problems to solve! Email yours to mac911@macworld.com, including screen captures as appropriate and whether you want your full name used. Not every question will be answered, we don’t reply to email, and we cannot provide direct troubleshooting advice.
Personal Software