![]() |
#1
|
|||
|
|||
![]()
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 |
![]() |
Tags |
conversion, number, text |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
takamaz | Mail Merge | 3 | 02-26-2018 05:13 PM |
![]() |
wobblypolarbear | Excel | 4 | 02-25-2017 08:23 AM |
![]() |
Maxwell314 | Excel | 11 | 11-06-2015 08:52 AM |
Case Sensitive (contains) Selection | apolloman | Excel | 2 | 07-12-2011 04:50 AM |
Saving as PDF causes prompt for printable region out of range | chrisxyz | Word | 0 | 02-22-2010 05:58 PM |