Normalize apostrophe-separated thousands

Where apostrophe grouping turns up

Swiss-style price lists, payroll sheets and statements often write thousands with an apostrophe, so 1'234.50 means one thousand two hundred thirty-four and a half. Copy that column into a spreadsheet with English settings and the apostrophe usually makes the cell plain text, so it drops out of any sum without warning.

The same character also lives in names such as O'Neill, in product notes and in abbreviations. That is why the normalizer never strips apostrophes across the whole table. It works on the one column you pick, and it accepts the apostrophe only in a strict digit-group pattern: one to three digits first, then groups of exactly three.

Settings for an apostrophe-grouped column

• Paste the rows into Table text and set Delimiter to the character that separates fields in your text.
• Tick First row contains column names when the first line holds headers, so the header is not converted or counted.
• In Number column, choose the amount column and nothing else.
• Set Decimal separator to . and Grouping separator to the apostrophe option.
• Press Calculate, then read Converted cells and Values left for review before you use Download (CSV).

Reading 1'234.50 step by step

With a decimal point and apostrophe grouping, the source value 1'234.50 becomes 1234.50. The grouping mark is removed, the two fraction digits stay exactly as written and nothing is rounded. An amount in the same column written without any grouping, for example 980.00, also converts under these settings.

A value such as 12'34.50 does not convert, because 34 is not a three-digit group. It stays in the cell exactly as typed and adds one to Values left for review, so you can go back to the source and see what was meant.

Converted values keep the point as the decimal mark, which is the form the decimal totals checker expects, so you can paste the converted column there to compare it with a stated total.

Pitfalls specific to apostrophe separators

• Leaving Grouping separator on comma: the apostrophe is then an unknown character and 1'234.50 is held for review instead of converted.
• Pointing Number column at a name or reference column, where apostrophes are part of the text.
• Curly apostrophes: some documents print a right single quotation mark instead of the straight ' character. The grouping option is the straight character only, so those cells stay unconverted until you correct the source text.
• Assuming a photographed statement was read correctly. In the Tablora app, the XLSX export also drops straight apostrophes from cells in columns it treats as numbers, so compare a few results with the Source · page view before you share the file.

Original synthetic example

Source value

1'234.50

Decimal separator

.

Grouping separator

'

Expected outcome

Normalized amount: 1234.50

Tools

• numeric-normalizer