Preserve CSV product IDs with a text transport copy in Google Sheets

For a small controlled import, make a separate transport copy with ID: prepended to every product identifier, then remove that prefix with MID after importing. Keep the original CSV untouched and accept the result only after exact character checks. This avoids depending on an import conversion checkbox.

The prefix is a reversible transport convention for this workflow, not part of the actual product ID. It makes the field visibly nonnumeric before a spreadsheet sees it. That import behavior is an expectation to test, not a blanket guarantee about arbitrary CSV files. Google’s documentation supports the import route and MID extraction; the complete workflow below has not been tested in Google Sheets.

When this approach fits

Use a desktop browser and a new Google Sheets file. The method suits a small export whose identifier field you can safely transform outside a spreadsheet, or a source system that can produce a separate text-prefixed export. It does not recover a file already saved with rounded IDs.

Do not edit a complex CSV with blind search-and-replace. Quoted commas, embedded line breaks, and escaped quotation marks require CSV-aware handling. If you cannot make a trustworthy working copy, stop and have the exporting system supply one. Do not upload sensitive production data to a third-party converter.

1. Retain the original and prepare a transport copy

The untouched synthetic source contains:

ProductID,Item,Quantity
000123,Notebook,2
12345678901234567890,Pen,3
03-04,Cable,4
1E10,Adapter,5

Open it in a plain-text editor. Keep it as the authoritative original; do not round-trip it through a spreadsheet first. Save the separate transport fixture as product-ids-transport.csv. Its records are:

TransportID,Item,Quantity
ID:000123,Notebook,2
ID:12345678901234567890,Pen,3
ID:03-04,Cable,4
ID:1E10,Adapter,5

Only the identifier field and its heading differ. Every data record receives exactly one additional ID: prefix. If a genuine product ID already starts with ID:, prepend another prefix in the transport copy; later remove only the first three characters.

This example’s simple ASCII fields have no embedded commas or quotation marks. It is intentionally small enough to compare every line with the original. Do not assume that a four-record manual method scales safely to an unreviewed large export.

2. Import the transport file into isolation

1. In a blank Sheets file choose File → Import → Upload and select the transport CSV.
2. Choose a new spreadsheet or new sheets rather than replacing production data.
3. Select Comma as the separator for this fixture.
4. Complete the import. Confirm four records occupy rows 2–5 under the header.

Google documents the CSV import destinations and separators and the Upload route. This procedure makes no claim about a conversion checkbox’s current wording or state.

Before extracting anything, compare each imported transport value with the working file in the text editor. In unused cells, =EXACT(A2,"ID:000123") and the corresponding row-specific checks should return TRUE. If any imported character differs, do not continue to the product lookup or overwrite the source.

3. Remove the transport prefix as text

In D1 enter ProductID text. In D2 enter:

=MID(A2,4,LEN(A2)-3)

Fill through D5. MID starts counting at 1, so position 4 is the first original character after ID:. The requested length is the transport length minus three. Google’s MID documentation

Expected D2:D5 values are 000123, 12345678901234567890, 03-04, and 1E10. The result is text extracted from a string; do not pass it through VALUE, multiply by 1, or add zero.

Before applying the formula to real records, check that each transport field has the expected prefix and contains an actual ID. A malformed field such as 000123 without ID: would be cut incorrectly. Do not silently remove three characters from an unchecked column.

4. Compare with the untouched original

Use three complementary checks:

=ISTEXT(D2) should return TRUE, and =LEN(D2) should return 6; fill both checks down. All exact comparisons should be TRUE. The expected strings above come from the untouched source, not from a second import that could repeat the same conversion error.

ISTEXT checks type; LEN counts characters; EXACT compares the actual strings, including case and spaces. A same-length wrong ID can pass a length check, so length alone is insufficient.

5. Keep business numbers separate

Quantity is a numeric field with a different purpose. In E1 enter Numeric quantity, then use =VALUE(C2) in E2 and fill down. Expected results: 2, 3, 4, 5, totaling 14. VALUE is appropriate for these quantities; it is inappropriate for the extracted product identifiers.

Keep A:D until reconciliation is complete so the transport and extracted strings remain auditable. Use D as the identifier key only after every required comparison passes. For a real file, also reconcile record count and a business total against the source; perfect IDs do not prove that no rows were omitted.

Export and failure recovery

An exact check fails: Return to the original text file. Locate the first point at which the characters changed: transport preparation, import, or extraction. Recreate the affected working copy; do not invent missing digits.

The prefix is in the business key: Use the extracted D column, not A. The prefix belongs only to transport.

The ID naturally contains ID:: Remove only the one prefix you added. A transport value ID:ID:001 correctly extracts to ID:001.

You export extracted IDs to CSV: CSV has no persistent spreadsheet cell-type metadata. The next importer can reinterpret a bare 000123 or long digit string. Inspect the exported file in a text editor, and agree on a text-safe import or another controlled transport copy with the receiving system. Never send prefixed IDs as real IDs unless the receiver explicitly expects that convention.

Only a damaged spreadsheet remains: Text formatting and substring functions cannot reconstruct unknown original digits. Obtain a fresh export from the source of record.

See the MID, LEN and EXACT references for the checks used here.

Testing disclosure: First-party import and function documentation reviewed October 16, 2026. The synthetic transport transformation, exact characters, lengths and quantity total were independently checked outside Sheets. This tutorial has not been tested in Google Sheets; actual CSV parsing must pass the explicit transport and extracted-ID checks before use.