In English countries, the decimal separator in Excel is a period. In others, like France, a comma is used instead.

This is not normally an issue, because Excel does the thinking in the background and if you open a South African document with 97.4 in cell A1, lo and behold it shall be displayed automatically as 97,4

However, difficulty arises when we convert text to numbers.

See attached example.

In English Excel, you can do VALUE("97.4") = 97.4
In French Excel, you can do CNUM("97,4") = 97,4

But in France, CNUM("97.4") = #VALUE
One way to solve this is to do CNUM(SUBSTITUE("97.4"; "."; ",")) = 97,4

But I fear this may now cause #VALUE in English countries.

Maybe a simple solution could be
IFERROR(VALUE("97.4"), VALUE(SUBSTITUTE("97.4",".",",")))

But that feels a little daring. Not sure why.

Can anyone come up with an alternative solution to make my number conversion robust for any region?

Thanks in advance!


P

Attached Files

Number conversion example.xlsx (10.6 KB, 9 views)