Prevent Numbers from resolving "equations" on importing

We have a database of items that uses the following system of categorization: category/sub-category, where both "category" and "sub-category" are numbers. So a unique identifier for an item might be "199/23"

Even though the field that holds this identifier in the database is a text field, when I export records from the database that includes this field and open the exported file in Numbers, Numbers will resolve the implied equation and "199/23" comes through as "8.6521739." Even if I then explicitly format that cell as text, since the conversion has already taken place, it does not revert to the way I need it to be.

I do not have the option of changing the database. How can I force Numbers to import "199/23" as a text string?

Until you find a better answer, you could use a word processor or text editor to replace the "/" character with a comma delimiter. This would expand your table by one column and eliminate the conversion. The delimiter would probably require the closing and opening quotes too, or whatever it would take to fully divide the field into two fields.

Would you mind sending me a small sample of your file to experiment with? Just a few rows.

If your comma-separated fields don't have commas within them, then you could use a word processor or text editor along the lines Jerry suggests to do a "replace all" from comma to tab (i.e. instead of / to comma). Then select all, copy, click once on an existing table in Numbers, and paste.

If your database has commas within a field, then life gets more complicated. Give a shout if you're in that situation.

I had already tried both csv and tsv and neither had worked. I couldn't get cut and paste to work either.

What I finally did was to open the tab-separated file directly in Pages and then turn the text into a table, which I could then sort by column.

This worked out best in the long run anyway, since the ultimate destination for the exported data was a Pages file. I thought I had to go through Numbers in order to be able to sort by column, forgetting that I could do the same thing in a Pages table.

That resolves the immediate issue, but it still would be nice to have a little more control over importing into Numbers. I'll let Apple know.

Numbers will open the tab-separated file, but it will resolve the "equation" while doing so. Opening the same file in Pages results in a file where each record is on its own line and the "fields" are separated by tabs. You can then do a Select All and under "Format->Table" choose "Convert Text to Table" and you get a nicely formated table, without the text being altered by the import process.

Numbers will open the tab-separated file, but it will resolve the "equation" while doing so.

Yes, that is what I get here when you try to File > Open a tab-separated file. The file will open, but you end up with a mess in Numbers. That is why I was saying instead of File > Open (as you would do with a comma-separated file) try opening the tab-separated file in a text editor, select all, copy, click once in a cell in an existing table in Numbers, and paste. That should work, and on my machine does not alter the text.

Nice to know Pages has that text-to-table conversion capability (I don't have Pages on the Mac yet.)

More Like This

Retrieving data ...

This site contains user submitted content, comments and opinions and is for informational purposes only.
Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site.
All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.