Preserve Long Order IDs When Importing CSV into Numbers

Quick answer: Keep an untouched source CSV. Apple’s documented Numbers import controls do not provide a per-column “import as Text” selector. For critical identifiers, prepare a separate transport copy with a nonnumeric prefix such as ID: before importing, then remove that prefix with a text formula. Validate against the source before using or exporting the identifiers.

This is a controlled workaround, not a claim that opening an arbitrary numeric-looking CSV preserves every digit. Changing a cell to Text after conversion cannot recover characters that have already been lost.

Requirements and evidence

This guide targets Numbers for Mac 15.4, the edition identified by Apple’s Mac guide when checked on October 16, 2026. Check your installed version in Numbers → About Numbers. These are Mac instructions, not the iPhone, iPad or iCloud interface. English formulas below use commas; a document that uses decimal commas may require semicolons between arguments.

Testing disclosure: The instructions were checked against Apple documentation and the sample results were independently calculated. This workflow has not been tested in the Numbers application; no application screenshots are supplied.

You need access to the original identifiers as strings before any spreadsheet conversion. The preparation step can be done by the system that exports the CSV or in a plain-text, CSV-aware tool. Do not create the prefixed copy from an already damaged Numbers import. CSV has no persistent cell-type metadata, so putting a value in double quotes is not an instruction to treat it as a text-typed spreadsheet cell.

Keep a difficult test fixture

The raw synthetic CSV is a reference fixture. Inspect it as plain text; do not assume a double-click import is lossless. The prefixed transport CSV is the file to import for this walkthrough.

Original OrderID	TransportID	Expected characters
00001234	ID:00001234	8
12345678901234567890	ID:12345678901234567890	20
12345678901234567891	ID:12345678901234567891	20
00000000000000000001	ID:00000000000000000001	20
1E10	ID:1E10	4
03-04	ID:03-04	5

The second and third identifiers differ only at the last character. They must remain distinct. The last two test whether something that looks like scientific notation or a date stays an identifier. The sample also includes a quoted description containing a comma to test parsing independently of identifier handling.

Prepare the transport copy before importing

For each original identifier, concatenate the exact literal ID: with the original string. Preserve all characters after the prefix, including zeros. Do not run numeric conversion, trimming, rounding or date parsing. Keep the original file unchanged.

The first transport record is ID:00001234, not ID:1234. Prefixing a damaged value only protects the damaged value. If you cannot obtain a trustworthy source string, stop and request a fresh source export rather than guessing missing digits.

Use a CSV-aware export when the data contain commas, double quotes or line breaks. A global search-and-replace on commas can corrupt field boundaries. The supplied transport fixture has three columns: TransportID, Description and Quantity.

Import and check the columns first

1. Drag the transport CSV onto the Numbers icon.
2. Before editing any cells, select the imported table. In the Format sidebar, open Table → Adjust Import Settings.
3. Choose Delimited. Use comma under Separate Values Using, the double-quote text qualifier and the appropriate Text Encoding for your source; the fixture is UTF-8.
4. Leave Treat consecutive delimiters as one off when empty fields are meaningful. Click Update Table.

Apple documents these controls and warns that import settings cannot be adjusted after the table has been edited. If you already edited it, reopen a fresh copy of the source for another import attempt. Apple: import text files.

Verify six data rows and three columns. The description Notebook, blue must occupy one cell. Every value in column A must start with ID:; inspect the full value, not a narrow column’s truncated display. If any input differs from the transport fixture, stop here.

Extract the original identifier as text

Use row 1 for headers, with the six imported records in rows 2–7. Add column D named OrderID, E named Characters, and F named RoundTripOK. Leave formula cells in a formula-compatible format such as Automatic.

Enter these formulas in row 2, then copy them down through row 7:

• D2: =MID(A2,4,LEN(A2)-3)
• E2: =LEN(D2)
• F2: =EXACT("ID:"&D2,A2)

MID begins at the fourth character, after the three-character prefix. LEN supplies the number of remaining characters. MID returns text, so do not wrap it in VALUE, multiply by 1 or add 0. Those numeric conversions defeat the purpose. Apple documents the text operation in MID, character counting in LEN, and case/content comparisons in EXACT.

For this fixture, E2:E7 should be 8, 20, 20, 20, 4, 5, and every F result should be TRUE. These checks are necessary but not sufficient: F compares against imported column A, so an error introduced before A reached Numbers could pass. Compare D against the untouched source as well.

For a direct sample check, =EXACT(D2,"00001234") should be TRUE. =EXACT(D3,D4) should be FALSE because those two 20-character IDs differ. Expected text in D6 is 1E10, not 10000000000; expected D7 is 03-04, not a date.

Validate the output before handing it on

Keep the .numbers working file with the transport column and checks. For a CSV handoff, agree which columns the recipient expects; do not accidentally supply the audit columns or prefix as part of the business ID.

Use File → Export To → CSV. Apple allows a separate file for each table or a combined file, and text-encoding choices under Advanced Options. Use a separate table/file for the intended deliverable where that avoids mixing audit data with the output. Apple: export formats.

Open the exported file in a plain-text viewer and compare all six identifier strings against the raw source. Do not judge export fidelity by reopening the CSV directly in Numbers: that runs a new import with a new opportunity for conversion. If the exported text is correct but the recipient’s spreadsheet changes it, the receiving import also needs a text-preserving workflow.

No export result is claimed here as observed in Numbers. Run this sample round-trip on your installed version before applying the method to real orders. Stop if any character differs.

Edge cases and recovery

• Missing ID: The formula assumes a three-character prefix followed by at least one character. A value that is only ID: needs separate missing-data handling; do not apply this MID formula blindly.
• Wrong prefix: Validate the source contract first. If A contains REF: or an unprefixed ID, removing exactly three characters is wrong.
• Zeros already disappeared: Return to the source. A custom zero-padded numeric format can change appearance but cannot prove the original ID length or recover rounded digits.
• IDs accidentally merged: Compare both 20-character test records. A matching length alone cannot detect a changed last digit.
• Whitespace differs: Do not trim automatically. Decide whether spaces are part of the identifier contract and compare exact strings accordingly.
• Raw values start with formula characters: This fixture contains no such values. Treat arbitrary external CSV as untrusted; do not turn it into spreadsheet formulas as an ID-preservation trick.

See the focused guides to MID, LEN and EXACT for their argument rules. The central rule is simpler: preserve first, check characters second, and never infer original identifiers from an already converted number.