Forum Discussion

Ilze Vilka's avatar
Ilze Vilka
Copper Contributor
Feb 11, 2018

Excel setting rule for digit count with decimal numbers

Hi there,

Need to set a rule for data input (in certain column) that only 11 digits with 2 decimal places are allowed to be entered. Have certain data to prepare for input for another database with certain limits.

IE: 12 345 678 900,00

and will be highlighted, if digit number exceeds allowed 11places 12 345 678 9000,00.

 

Could use len command, if not decimal places, but can not find a way to set a rule for limiting numbers with decimal places.
Can anyone help with a rule for this?

excel
Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 11, 2018

    Hi Ilze,

     

    The rule for number of decimal places could be

    =(IF(INT(A1)=A1,0,LEN(A1)-SEARCH(".",A1))=2)

    you may combine it with total 11 digits length