Normalize decimal-point amounts explicitly

The decimal-point convention

In the United States, the United Kingdom, Japan and many other places, a point marks the fraction and commas group the thousands, so 1,234.50 is one thousand two hundred thirty-four and a half. The same characters swap roles in decimal-comma countries, which is why a setting that is right for one source can quietly reverse amounts from another.

The normalizer never decides the convention for the whole file. You choose the separators for one column at a time, and only that column is converted. Identifiers, product codes and descriptions in other columns keep their exact characters.

The browser form converts one Number column per calculation. For a second amount column, paste the downloaded result back into Table text and run it again with that column selected.

Converting a point-decimal column

• Paste the rows into Table text and choose the Delimiter. If the amounts contain grouping commas, the fields themselves should be separated by tab, semicolon or pipe, or the amounts must be quoted.
• Tick First row contains column names if there is a header row.
• Select the amount column in Number column.
• Set Decimal separator to . and Grouping separator to comma.
• Press Calculate and read Converted cells and Values left for review.

What happens to 1,234.50

With these settings the source value 1,234.50 becomes 1234.50. The grouping comma is removed and both fraction digits are kept exactly. A plain 1,234 becomes 1234, and 0.75 stays 0.75.

Some values are held rather than guessed. A fraction written without a leading zero, such as .75, is held for review. So is 1,23.00, because 23 is not a three-digit group. Each held value keeps its original text in the output, so you can look it up in the source.

Mixing up the two conventions

• If you select a decimal comma by mistake, 1,234.50 is not turned into a smaller number. It cannot be read under those settings and appears under Values left for review, which is your cue to check the separators.
• Do not run one setting over a file built from sources with different conventions; split it by source first.
• Review every held value separately instead of deleting it; a held cell often points to a typo or an OCR misreading in the source.
• In the Tablora app, the XLSX export works out the decimal mark for each number column from the values that are unambiguous. A column where every value looks like 1,234 has no such evidence, so check a few exported cells against the page.

Original synthetic example

Source value

1,234.50

Decimal separator

.

Grouping separator

,

Expected outcome

Normalized amount: 1234.50

Tools

• numeric-normalizer