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")"
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.
The problem is that the first is a legitimate formula, though probably not often used that way.
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.