When I use the formula "=IF(LEN(A22=9),"Valid","Invalid")",the result is Valid, whereas it should be giving Error message
When I use the formula "=IF(LEN(A22=9),"Valid","Invalid")",the result is Valid, whereas it should be giving Error message, because the correct formula is "=IF(LEN(A22)=9,"Valid","Invalid")"
3 comments

Harlan Grove commented
Excel doesn't have strong types. A22=9 is evaluated the same as A22="9", which returns either TRUE or FALSE. Those possible return values are converted to strings, so LEN(A22=9) returns either 4 or 5, and IF treats any nonzero numeric value as TRUE. Thus the entire formula always returns "Valid".
This is just the way Excel works and has worked since the mid 1980s. There are likely several orders of magnitude more Excel users who need it to continue to work as it currently does than would prefer your revised formula evaluation semantics. Don't expect this to change.

dogknees commented
The problem is that the first is a legitimate formula, though probably not often used that way.

Anonymous commented
Never heard of this before but apparently when you don't use logical operators with numbers, it always returns TRUE.
Same happens in VBA and older versions of Office which I think is weird.