Conditional formatting for decimals

[Ranjit1960]

Hello Experts,

I am creating an excel sheet where user will have to put values with 4 decimal places. The value will turn red if it does not have four decimal places (i.e. more or less decimals places). I formatted the cells as number with four decimals and red font, so all input values become red (regardless of decimals places). Then, I applied the conditional formatting with formula =LEN($D6&"")-FIND(".",$D6&"")=4, and conditional formatted the font black, so all input values with four decimals turn black. Everything works fine till this stage.

I have an issue while I put value with four decimals places, but zero at fourth decimal place (e.g. 1.1110). The excel doesn't consider trailing zero at a decimal, and doesn't convert the value to black. I am sure I am missing something here.

Your support will be highly appreciated.

Thanks in advance!!

[#2]

You'll probably have better response if you post your Excel question in an Excel-specific forum, rather than this one, which supports Access.

[#3]

you can use this condition, where A1 is the start of the range to format:

=AND(ISERROR(LEN(A1)-FIND(".",A1))=FALSE, LEN(A1)-FIND(".",A1)<>4)

[#4]

@arnel_gp
Thanks for the formula! This is still not giving me the required result. Now I started getting the values with all the decimals turning black.
I want just the four decimals values turning black, even if the fourth decimal is zero.

[#5]

i don't think it would be possible with Trailing 0, since Excel automatically remove them if the the cell is not formatted as Text.