How to use the currency text column normalizer
- Choose how the numbers in your column are written: US style, European, Swiss, French or Indian grouping.
- Paste the amounts, one per line.
- Read the plain number for each line and the note beside it.
- Check the lines marked to check and fix any that were not converted, then copy the table or the CSV.
Formula
negative: parentheses, a leading minus, a trailing minus
remove currency signs and 3 letter codes at either end
digit groups must match the style, for example 3 digits after the first group, or 3 then twos for Indian
result = optional minus + integer digits + "." + fraction digits
The decimal mark and the grouping mark come from the style you choose and are never guessed from the values. Digits are handled as text, so large amounts keep every digit.
A US style column
With the US style, $1,234.50 becomes 1234.50, (2,500.00) becomes -2500.00 and 12.5- becomes -12.5. The cells 1,000 and USD 3,400 become 1000 and 3400 but are marked to check, because in many European styles 1,000 would be one with a decimal comma.
With the European style, 1.234,50 becomes 1234.50, (2.500,00) becomes -2500.00, 12,5- becomes -12.5 and 3.400,99 becomes 3400.99. The cell 1.000 is converted to 1000 and marked to check.
Why you choose the style
A cell such as 1,234 means one thousand two hundred thirty four in US style and one and a bit in a style that uses the comma as the decimal mark. No program can tell which from one cell, so a tool that guesses can silently produce numbers that are wrong by a factor of a thousand.
This page asks you to choose, and it marks the cells that would read differently in the other style. Cells that do not fit the chosen style, such as 1.234,56 read as US style, are refused instead of being guessed.
What is and is not changed
Currency symbols and codes are removed, not converted, so $10 and EUR 10 both become 10. Keep a separate column for the currency if you need it. Parentheses and minus signs both become a leading minus sign.
The output is plain text with a dot as the decimal mark and no grouping, ready to paste into a spreadsheet or to add up with the Sum Numbers in Text Calculator.
Assumptions and limits
- Every cell in the column uses the one number style you chose.
- Currency signs or codes at either end of the cell are removed and not converted.
- Grouping is checked strictly. A cell with the wrong group size is refused.
- No rounding is done, so the digits after the decimal mark are kept as typed.
- Up to 5,000 amounts are read.
Frequently asked questions
How are negative amounts handled?
Parentheses, a leading minus sign, the typographic minus sign and a trailing minus sign all become a leading minus. A negative zero is written as zero.
What does a cell marked to check mean?
It was converted, but it would read differently in another style. It is worth a look if your column might mix styles.
Why was a cell refused?
Its digits do not follow the style you chose: for example, a group of two digits where three are needed, a second decimal mark or letters in the middle.
Can I convert a column of mixed styles?
Split the column by source first and convert each part with its own style. Mixed styles in one column cannot be told apart reliably.