Which is the correct decimal format of your operating system?
N = YourImportedNumberField ;
Left ( Right ( N ; 3 ) ; 1 ) = "." ; Div ( Filter ( N ; 1234567890 ) ; 100 ) & "," & Right ( N ; 2 ) ;
This calculation assumes that there are always two decimals in the imported number and that it is always positive.
( sure there are other better calculations )
But its not allways 1,000.00 oder 1.000,00 it can be even 1000 or 1000,00 or 1000.00 or 1000,0 or 1000.0 or maybe even 1,000 or 1.000
The best would be probably to detect if there is
format to delete the left , or . and then to look if there is
with one or two x behind the decimal formator (right x,x) to make it allways xxx,xx.
Or is there a better way?
Or is the left , or . necessary?
The position function could be used to scan the text from right to left for the first comma or period. You'd then need to check also to be sure that there are no more than two digits to the right of this character to make sure that you don't have a thousands separator with no decimal places.
And if three decimal places are possible, it may not be possible to tell if you have a value of 1 or 1 thousand in some cases.
At least for my area I could asume that I have allways more then full positive 10 (10$ for example).
But how could that look like?
Let ( [ T = YourText ;
Ln = Length ( T ) ;
Dc = Max ( Position ( T ; "." ; Ln ; -1 ) ; Position ( T ; "," ; Ln ; -1 ) )
If ( Ln - Dc > 2 ; Filter ( T ; 9876543210 ) ; Filter ( Left ( T ; Dc -1 ) ; 9876543210 ) & "," & Left ( Right ( T ; Ln - Dc ) & "00" ; 2 ) )
Awsome! Whatever I used I got xxxx,xx
Btw. Christian - http://www.mbsplugins.eu - today released an extension to his "Math.TextToNumber" function that can detect the decimal format and convert it to your regional format. Not that big difference to the abouve, but very nice to have it in a short working no need to have brain solution :)