AI Spreadsheet Cleaning: Keep Every Product Code Intact

Imagine two product codes in a catalogue: 001204 and 1204. Your business treats them as different identifiers. If an import converts both to the number 1204, a later request to “remove duplicate codes” starts from information that has already changed.

An AI assistant’s cleaning proposal can be perfectly consistent with that altered spreadsheet and still conflict with the original catalogue. Before asking it to standardise columns, find duplicates or prepare a report, decide which fields identify things and must retain their exact characters. That decision belongs at the start of the workflow.

Decide what each column means

A quantity describes how much. An identifier tells you which item or record you mean. Product codes, booking references and membership IDs may contain digits while serving as labels. Their meaning comes from the system that issued them.

Write a short rule for each column. For example: “ProductCode is text, including any leading zeroes; Units is a whole-number quantity; Description is editable prose.” This is a proposed specification for the worked example. Another dataset may have different rules, including whether letter case, spaces or punctuation are significant.

Microsoft’s guidance on leading zeroes and large numbers explains that Excel can remove leading zeroes and convert number-like text. It also documents a maximum numerical precision of 15 significant digits. A longer identifier stored as an Excel number can lose digits beyond that limit. Preserving it as text avoids treating its characters as a calculation.

This problem predates AI cleaning

A study published on 30 July 2021 by Mandhri Abeysooriya and colleagues examined gene-name errors in supplementary spreadsheets from genomics publications dated 2014–2020. The researchers found identifiers converted to dates and other numerical representations. Their work illustrates a consequential conversion problem; it does not measure errors in today’s AI assistants or establish a current rate for business spreadsheets.

The practical lesson is about the whole route a value takes. Check the source, import and transformation steps before deciding that a strange result originated in an AI answer. A later explanation cannot establish which characters existed before a file was opened or changed.

Choose types during import

Microsoft’s Power Query documentation says automatic type detection is enabled by default for unstructured sources such as CSV files. Its automatic column-type and header detection uses the first 200 rows and adds a Changed Type step. A preview is therefore useful evidence about an import, with a defined inspection scope.

For CSV data, the Text/CSV connector documentation describes options to infer types from the first 200 rows, inspect the entire dataset or disable automatic detection so columns initially default to Text. It notes that capabilities can vary between host products.

A practical approach is to begin with a preserved source file and explicitly protect the identifier columns before loading the working data. Then assign suitable numerical types to quantities that need arithmetic. Review the transformation steps so an earlier conversion has not already altered the identifiers. If it has, return to the untouched source and change the import procedure.

Keep that distinction when requesting help: “Store every column as text” may be a useful initial import choice, but the final working sheet still needs deliberate types for the calculations you intend to perform.

Use a small example that exposes lost identity

Consider this fictional two-row stock file. The original source records are:

• Row A: ProductCode 001204, teal cover, Units 2.
• Row B: ProductCode 1204, purple cover, Units 5.

The total is seven units. If both codes become 1204 while the other fields remain unchanged, the sheet still has two rows and seven units. Those checks pass even though one identifier has changed. A total cannot answer whether the teal cover still has its correct code.

For this example, the acceptance rule is exact preservation of both source codes and their associated descriptions and quantities. Compare records using their original row reference or another independently preserved reference, rather than trying to match them only through the field whose integrity is in question.

Extend your fictional practice file with a 16-digit code and a code containing a literal E, such as 1E10. Define both as identifiers and require their exact original text. These are suggested checks against stated requirements, not a claim about how frequently an AI system makes a particular mistake.

Give AI a transformation boundary

Replace a vague request to “clean everything” with a description of permitted changes. An example instruction is:

“Help me plan an Excel import for a catalogue. ProductCode is a text identifier: preserve every character, including leading zeroes and letter case. Units is a numerical quantity. Keep descriptions associated with their original records. Propose the import and cleaning steps before changes are made. Do not infer missing codes or merge records because their codes look similar. List ambiguous entries for review, and explain how I can compare the output with the untouched source.”

That instruction defines the job; it does not verify the result. Examine the proposed steps, run them on the small practice file and compare the output with the expected records. If the assistant proposes a formula or script, check what it does to the identifier column before applying it more widely.

A display change cannot establish the original value

Microsoft distinguishes text preservation from custom number formatting. A display format can make numerical codes appear with a specified pattern of zeroes. That appearance is insufficient evidence that an unknown original identifier has been recovered. If information was lost, use the authoritative source to restore it.

For example, knowing that one valid code was 001204 and another was 1204 is essential before applying a six-character padding rule. Adding zeroes to everything would change the second code, contrary to this example’s specification. A formatting decision needs a business rule, not simply a preference for tidy columns.

Check identity before trusting the calculation

When you approve the cleaned file, retain the source version, the column rules, the transformation steps and the comparison results. Include a count of unresolved entries. Check the file through the import route that the next person or system will actually use.

Once the records are intact, the next question is what a calculation includes. MaryChuks’ guide to defining what an AI spreadsheet average counts shows how valid zeroes, blanks and missing information change a metric.

Useful AI assistance begins with a clear meaning for each field. Protect the labels that connect your records to real items, then make the transformations needed for analysis. A clean spreadsheet should preserve the distinctions your business relies on.

Sources

• Microsoft Support: Keeping leading zeros and large numbers.
• Microsoft Learn: Data types in Power Query.
• Microsoft Learn: Text/CSV connector.
• Abeysooriya and colleagues, Gene name errors: Lessons not learned, PLOS Computational Biology, 30 July 2021.

Product documentation checked on 24 October 2026. The catalogue records and suggested AI instruction are illustrative.