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.
- 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.
Now get back to eating turkey and watching football games, and I’ll see you back here tomorrow, for the Black Friday Sale.