The import function in Numbers for iOS, iPadOS, and macOS can take knowledge within the widespread comma-separated values (CSV) and tab-separated values (TSV) codecs and parse that proper right into a spreadsheet’s fields. Nonetheless, you could have encountered an issue for those who usher in knowledge that was exported from elsewhere or formatted by hand that shouldn’t be interpreted as a quantity however, quite, as plain textual content.
As an illustration, the United States Postal Service Zip code system is likely one of the most typical makes use of of main or padding zeroes in numbers you cope with day by day. In a zipper code like 04843, the preliminary zero is a crucial a part of the Zip code—however for those who import a CSV or TSV file with 04843 in a discipline by itself, Numbers (like Excel) routinely treats it like a quantity. The column applies numeric formatting routinely and trims off that zero when changing it to a pure quantity: 4843.
The error is silent, too. If you happen to’re not paying consideration, the primary time you understand one thing is incorrect is when the submit workplace rejects an tackle, otherwise you encounter comparable validation issues with much less generally occurring numbers which have main zeroes or different formatting that triggers Numbers decoding the sphere as a quantity.
There’s a straightforward method to work round this, one that individuals utilizing Excel have used for years, but it surely’s oddly not a regular possibility in any software I’ve used that exports CSV or TSV recordsdata. Within the CSV or TSV file, as a substitute of formatting a quantity by itself like ,04843, or <tab>04843<tab> you add an equals signal and enclose the quantity in citation marks, like:
="04843"
Numbers (and Excel) reads this as straight textual content. After importing, you possibly can choose the column and explicitly set it to textual content through the Format Inspector > Cell > Knowledge Format, the place you select Textual content.
Now, what for those who can’t management how your CSV or TSV is made? Add a processing step earlier than you open or import the file into Numbers. In an app like BBEdit (together with its free model), use Discover and Change. Listed below are the directions for BBEdit after making a backup copy of your file:
- Open the CSV or TSV file in BBEdit.
- Select Search > Discover or press Command-F.
- Ensure the checkboxes within the Discover dialog are set as follows: “Case delicate” and “Complete phrase” are deselected and “Grep” and “Wrap round” are chosen.
- Within the Discover discipline for a CSV file, enter
,(d+?),; for TSV,t(d+?)t(I’ll repeat these under for readability). - Within the Change discipline for a CSV file, enter
,="1",; for TSV,t="1"t. (Ditto.) - Click on Change All.
- Select File > Save or File > Save As.
- Open the CSV or TSV file in Numbers. Be aware the main zeroes!
BBEdit (proven right here with a TSV substitute) and different text-editing packages can allow you to simply repair a CSV or TSV file to stop the lack of main zeroes.
Foundry
These patterns above say “discover any quantity between commas (CSV) or tabs (CSV) and change the quantity with =”quantity”. Whereas this forces all imported numbers to be handled like tet, you need to use the Format Inspector to alter the cell formatting for any column you prefer to have Numbers act on these columns’ values as numbers.
For extra readability for step 4 and 5, right here’s what you copy and paste in every discipline for CSV:
- Discover in CSV:
,(d+?), - Change in CSV:
,="1",
And for TSV:
- Discover in TSV:
t(d+?)t - Change in TSV:
t="1"t
Be aware that these are straight citation marks within the substitute patterns for CSV and TSV, not curly or typographers’ quotes.
Ask Mac 911
We’ve compiled an inventory of the questions we get requested most ceaselessly, together with solutions and hyperlinks to columns: learn our tremendous FAQ to see in case your query is roofed. If not, we’re at all times on the lookout for new issues to unravel! E mail yours to mac911@macworld.com, together with display captures as acceptable and whether or not you need your full title used. Not each query will likely be answered, we don’t reply to electronic mail, and we can not present direct troubleshooting recommendation.
