11 Replies Latest reply on Mar 13, 2015 4:16 PM by firebase

# Detecting Decimal format

### Title

Detecting Decimal format

### Post

I have a script that is reading out prices from webpages. Its easy to detect the sourounding code and mark it as the regions where the price would be to read out.

My problem is that I am not able to find the right way to detect the right decimal number format.

is can be

1.000,00

1,000.00

1000,00

1000.00

And what ever is possible.

I used getasnumber in the field I want to write this into but there needs something else to be done.

Untill now I tried to convert the "," and "." trough different IFs into the right format but it was allways buggy.

Any help?

• ###### 1. Re: Detecting Decimal format

Which is the correct decimal format of your operating system?

• ###### 2. Re: Detecting Decimal format

Its like

1.000,00

• ###### 3. Re: Detecting Decimal format

Try:

Let(
N = YourImportedNumberField ;
Case(
Left ( Right ( N ; 3 ) ; 1 ) = "." ; Div ( Filter (  N ; 1234567890 ) ; 100 ) & "," &  Right ( N ; 2 ) ;
N
)
)

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 )

• ###### 4. Re: Detecting Decimal format

Thanx!

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

• ###### 5. Re: Detecting Decimal format

The best would be probably to detect if there is

x,xxx.xx

or

x.xxx,xx

format to delete the left , or . and then to look if there is

,xx

or

.xx

with one or two x behind the decimal formator (right x,x) to make it allways xxx,xx.

Or is there a better way?

• ###### 6. Re: Detecting Decimal format

Or is the left , or . necessary?

• ###### 7. Re: Detecting Decimal format

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.

• ###### 8. Re: Detecting Decimal format

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?

• ###### 9. Re: Detecting Decimal format

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 ) )
)

• ###### 10. Re: Detecting Decimal format

Awsome! Whatever I used I got xxxx,xx

Thanx alot!

• ###### 11. Re: Detecting Decimal format

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 :)