Before AI removes the comma in 1,234, check the source’s number rules

The same comma can do different jobs in different reports

Ask the exporter how the field represents a number before choosing a destination format. P1 uses a decimal point and grouping comma; P2 reverses them. Simply removing the comma from P2’s 1.234,50 produces 1.23450, whose value is 1.2345 rather than 1234.50. It still looks like a valid number, so the defect need not become an error message.

This is an original fictional weight-adjustment report, with every record in kg. P1–P4 are explicit source contracts, not national conventions inferred from file language, a colleague’s location or a country name. A negative adjustment means a deduction under this exercise’s contract; the calculation is not an audit of real stock.

Microsoft’s NUMBERVALUE reference supports explicit decimal and grouping separators. Omitting them uses the current locale. A function can convert under a chosen rule without establishing that the rule matches the author’s meaning.

Keep the original string, rule ID and unit

The complete input below is editorial teaching material, not real company data or a recorded AI response. N03 contains an actual U+202F character between 1 and 234. Do not remove every kind of whitespace before copying it. N05 deliberately lacks a source rule; N06 has a genuinely empty field.

CSV quotes surround fields containing commas; they are not part of the numeric value and do not guarantee that a spreadsheet will retain it as text. Microsoft’s Text/CSV import guidance supports disabling automatic type detection. Import raw_value as text to preserve the original string before converting it with the source’s rules. If automatic conversion has already happened, re-import the preserved source CSV; changing the display format cannot restore the original value. If fields have shifted, use the CSV boundary and column-type guide to repair that structure before interpreting decimal marks.

row_id,raw_value,rule_id,unit
N01,"1,234.50",P1,kg
N02,"1.234,50",P2,kg
N03,"1 234,50",P3,kg
N04,(20.00),P4,kg
N05,"1,234",UNKNOWN,kg
N06,,P1,kg

Ask for a review ledger rather than an overwritten source column

Require AI to explain the source rule used for each record, retain raw_value and keep unresolved rows in the result. A neat canonical column then remains traceable to the original strings.

Use this request after the complete CSV above. It organizes this exercise; no execution of Excel or a particular model is claimed. Reference answers do not measure a model’s success rate.

Use the complete CSV above and the following contracts. Preserve all six records, row_id, raw_value and unit; do not overwrite source columns.
P1: decimal point; comma as a three-digit grouping mark.
P2: decimal comma; point as a three-digit grouping mark.
P3: decimal comma; U+202F as the three-digit grouping mark.
P4: P1 separators, with parentheses enclosing the entire value meaning a negative adjustment.
UNKNOWN: the source convention is absent. Do not choose from appearance, language or common habits.
Return normalized_value and parse_status (confirmed/ambiguous/missing), plus the basis and unresolved candidates. Keep both 1234 and 1.234 for N05 without confirming either. Preserve N06 as missing, not zero. Identify U+202F in N03.
Sum confirmed records only, label the confirmed subtotal and explain why the total for all six remains unknown. Do not convert units, strip punctuation globally or describe the calculation as a real stock audit or a measured model test.

The full answer: four confirmed, one ambiguous and one missing

For N01, P1 removes grouping commas. For N02, P2 removes grouping points and normalizes the decimal comma. For N03, remove only the declared U+202F grouping character under P3. All three represent 1234.50. For N04, first confirm that parentheses enclose the entire value, then retain the negative sign specified by P4: −20.00.

The confirmed subtotal is 1234.50 + 1234.50 + 1234.50 − 20.00 = 3683.50 kg. N05 and N06 remain in the delivery ledger without entering that subtotal. No rounding contract supplies an unknown value, and there is no evidence that missing means zero.

row_id,normalized_value,parse_status,unit
N01,1234.50,confirmed,kg
N02,1234.50,confirmed,kg
N03,1234.50,confirmed,kg
N04,-20.00,confirmed,kg
N05,,ambiguous,kg
N06,,missing,kg

Convert known rules without letting defaults decide missing meaning

Read the CSV into A:D: A is row_id, B raw_value, C rule_id and D unit. For N02 in B3, whose P2 rule is confirmed, the candidate below explicitly chooses decimal comma and grouping point while preserving an empty input before conversion. It is an editorial candidate to execute and check in your target application.

This is not an automatic detector for the entire column. Applying it to P1, P3, P4 or UNKNOWN does not make those meanings correct. N03 requires its actual grouping character; N04 also needs its declared sign contract. N05 needs source clarification before a conversion function can provide a supported answer.

Microsoft also documents that NUMBERVALUE returns zero for an empty string. A zero returned by that default does not establish an observed zero in the source. Retain raw values and statuses, using the missing-versus-recorded-zero checks to keep the distinction.

=IF(B3="","",NUMBERVALUE(B3,",","."))

Repair individual interpretations before recomputing the subtotal

A faulty teaching draft might return 1.2345 for N02, positive 20 for N04, a confirmed 1234 for N05 and zero for N06. Those are four different defects. Asking AI to recalculate the grand total does not resolve them; the arithmetic can be accurate for the wrong inputs.

Restore each source string and contract first: reread N02 under P2, restore N04’s negative sign, return N05 to candidate status and N06 to missing. Then request all six records and the confirmed set of precisely N01–N04 before recomputing 3683.50. Editing only the final sentence leaves faulty details available for the next export.

Deliver the confirmed subtotal together with unresolved work

Include the original CSV, rule table, normalized ledger, confirmed record set and the questions for N05/N06. Ask the N05 source what role its comma plays. Ask whether N06 was unfilled, unmeasured or subject to another stated convention, rather than asking AI to invent a complete-looking total.

If N05 is later confirmed as 1.234, retain all three decimals before applying a separately agreed aggregation or display policy. Other rows displaying two decimal places do not justify prematurely turning it into 1.23. Numeric meaning, calculation precision and presentation remain different decisions.