Prompt: read French and English amounts without a thousand-fold error
“1 234,50” and “1,234.50” are the same amount; “1,234” can be a thousand times off. Normalize amounts by locale and send the ambiguous ones to review instead of guessing.
Works with: ChatGPT, Claude, Copilot, Gemini
The task
Convert amounts typed in French and English formats into one decimal format, and stop the ambiguous ones before they reach a total.
Normalize these fictional amount strings to a decimal string with two digits after the decimal point. Preserve id, original text and currency. For fr-CA, a space groups thousands and a comma marks decimals; for en-CA, a comma groups thousands and a period marks decimals. If locale is missing, return REVIEW with no normalized amount; never guess the separator meaning. All currencies below are explicitly CAD. Return id, original, locale, currency, normalized_amount and ACCEPT/REVIEW. Sum ACCEPT rows only and list excluded IDs beside the total. Do not convert currencies or add tax. L1: locale=fr-CA; currency=CAD; amount="1 234,50" L2: locale=en-CA; currency=CAD; amount="1,234.50" L3: locale=missing; currency=CAD; amount="1,234"
How to use it
- Run it with the three fictional amounts: L3 has no locale on purpose.
- Use it on a small sample of your own export, with a locale column if you have one.
- For a recurring import, turn the same rules into a tested formula or script; use the AI to find the edge cases.
What you get
A table with the normalized amount and ACCEPT or REVIEW for each line, a total of the ACCEPT lines only, and the excluded IDs named beside it.
| id | original | locale | currency | normalized_amount | status | |---|---|---|---|---|---| | L1 | "1 234,50" | fr-CA | CAD | 1234.50 | ACCEPT | | L2 | "1,234.50" | en-CA | CAD | 1234.50 | ACCEPT | | L3 | "1,234" | missing | CAD | (none) | REVIEW | - L1: Under fr-CA, the space groups thousands and the comma marks decimals. That gives 1234.50. - L2: Under en-CA, the comma groups thousands and the period marks decimals. That gives 1234.50. - L3: No locale is given, so the comma's role is unknown. Under en-CA rules it would read as 1234.00, and under fr-CA rules as 1.234. I left it unnormalized for review rather than guess. Total of ACCEPT rows: 2469.00 CAD (excluded: L3) I didn't convert any currency or add tax.
Where should you start with AI?
The free assessment gives you, in 4 minutes, the 3 tasks to automate first in your business.