Happy Thanksgiving, if you’re celebrating today. Tomorrow is Black Friday – the biggest shopping day of the year, in the USA. Up here in Canada, we celebrate the day too, so I’ll put one of my Excel products on sale for the day. Remember to check this blog tomorrow, to get the coupon code.
If you’re doing your shopping around the world, you might end up with a worksheet filled with amounts in a different currency. Let’s see how Excel can help with that problem!
Different Currencies
For example, here are the settings for two countries’ currencies:
- In the USA, a comma is the Thousands separator, and a period is the Decimal separator — 987.654,32
- In Germany, a period is the Thousands separator, and a comma is the Decimal separator — 987,654.32
Fortunately, there is a quick way to fix things in Excel.
You can use a built-in tool to change numbers from a German format, to the USA format.
Use Text To Columns
To fix currency that is in a format with different separators, use the Text To Columns command to convert the values.
Here are the steps that I followed:
- Select the cells that contain the numbers
- Choose Data>Text to Columns
- In the Text to Columns window, click Next, twice
- In Step 3, click the Advanced button
- From the Decimal separator drop down, select the separator that is currently used in the values — “,” (comma) in this example
- From the Thousands separator drop down, select the separator that is currently used in the values — “.” (period) in this example
- Click OK, then click Finish.
Tip: There are more examples for using the Text to Columns feature on my Contextures site. It’s a useful tool in lots of Excel situations!
Now get back to eating turkey and watching football games, and I’ll see you back here tomorrow, for the Black Friday Sale.
________________
Very useful, thank you