Convert Currency With Different Separators

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. For example,

• 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.

1. Select the cells that contain the numbers
2. Choose Data>Text to Columns
3. In the Text to Columns window, click Next, twice
4. In Step 3, click the Advanced button
5. From the Decimal separator drop down, select the separator that is currently used in the values -- "," (comma) in this example
6. From the Thousands separator drop down, select the separator that is currently used in the values -- "." (period) in this example
7. Click OK, then click Finish.

Now get back to eating turkey and watching football games, and I'll see you back here tomorrow, for the Black Friday Sale.

________________

8 Responses

1. XLarium says:

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

I think there is something wrong here.

I hope I used the correct HTML tags.

2. Sumit Bansal says:

A couple of years ago, I was working on a project to analyze financial data of the US (uses comma) and Latin American (uses decimal) firms. I wasted so much time to make the data consistent. This would have saved me weeks. Thanks for sharing!

3. Fiona Merali says:

I have the same issue, however the instead of . or , I end up with a download of spaces instead. Can you assist?
6 130.12. With this format, it is impossible to even add the numbers, as it is not recognised as a number.
Thanks for helping find a magical soution.

• @Fiona, in Step 3 of the Wizard, when you click the Thousands drop down, a blank is the third choice in the list. Use that, and select a period for the Decimal separator.

4. Fiona Merali says:

I tried but it separates the amount into 2 cells ie the 6 in one cell and the 130.12 in another. Thanks for coming back to me. Best Regards

1. December 9, 2014

[…] Got numbers with different decimal point symbols? Here is a fix […]