Mixed currencies

• Sales, expenses, or donations from several countries were gathered in one sheet.
• The currency was shown by cell formatting, which does not survive export to plain text.
• One office assumed its own currency was understood and left the marker off.

• A total adds amounts that are not comparable, and the result means nothing.
• Rankings and thresholds favor whichever currency has the larger numbers.
• Symbols in the cell make the column text, so calculations skip those rows.

• Search the column for currency symbols and three-letter codes, and count the rows that have neither.
• Compare the range of amounts by country or office. One group far above or below the rest suggests a different currency.
• Check whether another column, such as country or billing entity, implies a currency the amount does not state.

• Split each cell into a plain number and a currency code in its own column.
• Settle unmarked amounts from the source, not from the size of the number.
• Convert only if a single currency is truly needed. Conversion needs an exchange rate and the date it applies to, and both come from outside the file.
• Keep the original amount and currency alongside any converted figure.

• Mixed unitsValues in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
• Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
• Negative numbers in several notationsNegative amounts are written with a leading minus, a trailing minus, parentheses, or a credit marker, all in one column.
• Case example: Marketing analyticsFive clients, five definitions of a campaign

TermsCurrency code, Unit of measure, Standardization, Parsing