Count number of decimal places
This tutorial shows how to count the number of decimal places using an Excel formula, with the IF, INT, LEN and FIND functions
Hard coded formula
Cell reference formula
|
GENERIC FORMULA
=IF(number=INT(number),0,LEN(number)-FIND(".",number,1))
ARGUMENTS GENERIC FORMULA
=IF(number=INT(number),0,LEN(number)-FIND(".",number,1))
ARGUMENTS EXPLANATION This formula uses the FIND function to return the position of a full stop from a number, including the full stop. The LEN function is then used to return the total number of characters from the selected number. The formula then subtracts the number derived by the FIND function, which represents the number of characters of the integer component, including the full stop, from the total number of character of the selected number to return the total number of decimal places. At the beginning of formula the IF and INT functions are used to return a value of 0 if the selected number doesn't have any decimal places, therefore, being only an integer number.
Click on either the Hard Coded or Cell Reference button to view the formula that has the number from which you want to count the number of decimal place directly entered into the formula or referenced to a specific cell. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return nth decimal number | How to extract the nth decimal number | |
| Count number of specific characters in a cell | How to count the total number of specific characters in a cell |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| LEN Function | The Excel LEN function returns the number of characters in a specified string | |
| FIND Function | The Excel FIND function returns the position of a specific sub-string within a string |