Ultimate Guide to Formatting Currency and Money Amounts in Your Data

Photo by Jason Leung on Unsplash

Standardizing, cleaning and normalizing currency & money amounts in data is relatively simple if all the amounts are from the same denomination and format. Such as the following amounts that are all USD and follow American numerical conventions:

However, standardizing currency amounts from different denominations and formats becomes terribly complex, as seen below. We have currency amounts in USD, CAD, INR and others. The numerical format also differs based on the country:

In this guide, we will walk you through the steps you need to standardize these various currency amounts into a proper numerical format as seen below.

Let’s get your money and currency data ready for analysis!

What causes inconsistent currency formatting?

There are 3 main culprits for this particular data cleaning issue IF the data is NOT stored as a number

1. Currency Identifier — there are multiple ways to refer to a currency. For e.g., USD, $ and US dollar all refer to the US Dollar and can be used in conjunction with the amount
2. Decimal Separator — different countries use either the dot (.) or the comma (,) to separate the decimal part of the currency amount. For e.g., the US will write $20.59 while Italy will write €20,59
3. Thousands Separator — different countries use either the dot (.) or the comma (,) to separate the different groupings of their numbers. For e.g., twenty-thousand dollars will be written $20,000 while twenty-thousand euros will be written €20.000

You can see the list of each country’s decimal separator here. The thousands separator is then the opposite of the decimal separator

How do I standardize money amounts?

Let’s go step by step and use the aforementioned list as an example

Step 1 — Remove Currency Identifier

We will remove the identifier for each currency amount. Whether it is a code (USD), a symbol ($) or the full form of the currency (US Dollar)

This is trivial if we know what currencies exist in our data beforehand. Then we can do a Find & Replace for the code, symbol and/or the full name of the currency.

Otherwise, we can make a list of all currency names, codes and symbols. Then go through each currency amount and remove either of the 3 identifiers that appear in your master list. The list will look like the following

You can find all the currency full names and codes here and all the currency symbols here.

While removing, you should also match the currency to its 3 letter ISO code and place the code next to the currency amount like the following. This is crucial to some future steps

Step 2 — Remove thousands separator

To convert all the amounts to proper numbers, we will need to remove thousands separator.

This is trivial if all the amounts have the same thousands separator such as a comma (,) or the dot (.) but it becomes super difficult if they are all different

If they are different, then you will need to figure out for EACH amount, what the thousands separator is. If you know which region the amounts were entered from, then you can use this list to figure out the separator

Otherwise, there are 2 ways to guess what it is

1. You can assume that the country of the currency is what the thousands separator is. For e.g., that USD will use the comma (,) and EUR will use the dot (.)
2. You can check for punctuation in the amount such as the comma (,) or the dot (.) and if either is there then check how many digits are after that punctuation. That may tell you what the thousands separator is

Once you have figured out what the thousands separator is, then we remove it and will be left with the following

Step 3 — Standardize decimal separator

Now, we need to standardize the decimal separator. Whether you want it to be the comma (,) or the dot (.)

If you have completed the above steps properly, then all your currency values should be left with 0 or 1 punctuation each. Whether it is the comma (,) or the dot (.)

So, let’s say we want the dot (.) to be the decimal separator. Then we go through each currency amount and replace any commas with dots (.)

Now we will have the following and our currency amounts are perfectly standardized to numbers. Hooray!

OPTIONAL Step 4 — Convert currency amounts

Let’s say you want to convert currencies to each other. For example, convert all the currencies to USD

For this, we will first need the currency code for each amount from Step 1

Then we need to get the current exchange rates for each currency to USD. You can use this website to get the exchange rates and place them next to each amount

Finally, we multiply the exchange rate by the currency amount

On June 4th 2021, this will look like the following

An Easier Way

As you can see, this gets out of hand really fast. Especially with global currencies and all the different separators.

And this is why we created www.cleanspreadsheets.com

You simply upload your spreadsheet, choose the column with your currency, and click clean.

Our algorithms then auto-magically do all the cleaning for you and you get this as the result. We automatically convert each currency to USD and provide the symbol, the name and the 3 digit ISO code. We support all G20 countries

If you want a custom app or project built using spreadsheets, you can check out our consulting service here: https://www.lovespreadsheets.com!

Happy Data Cleaning!