Changing decimal separator with formula

Question

I work with teams from multiple countries and sometimes I receive excel files that have another decimal separator than the one I use. Is there any formula to do that? I certainly could use a macro, but a formula would be much easier because I am not the only person in my company in need of this and a macro would be too complicated for the general Joe Public.

Please keep in mind that I may have multiple separators in the same number, for example, 3.000.000,00 has 2 points for the thousands and millions, and the comma for the decimals. That is why I am finding it difficult.

Answer 1

Alternatively, you can use the NUMBERVALUE function.

For example, if I have 3.000.000,00 in cell A1 I can enter =NUMBERVALUE(A1,",",".") in cell B1 to get the number

The arguments are

• Text. The text to convert to a number.

• Decimal_separator. The character used to separate the integer and fractional part of the result.

• Group_separator. The character used to separate groupings of numbers, such as thousands from hundreds and millions from thousands.

This is available in Excel 2013 and later or Excel 2011 for Mac or later. I work in an Excel 2013 environment and sometimes forget to qualify my answers with that. Thanks to Doug for pointing out the version requirements.

Specifically:

Excel 2016, Excel 2013, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel Mobile, Excel for Android phones