VBA IsNumeric going WILD

Question

Please explain why the below code behaves randomly

The below line of code returns TRUE when it should have return FALSE

?Isnumeric("555-")

Also

?Isnumeric("555-"/2) returns TRUE

Please explain this random behavior of IsNumeric?

Answer 1

Although it is a bit esoteric the trailing minus is a valid numeric format sometimes used in accounting packages. I guess it is not used so much nowadays. It indicates a negative number e.g. 555- is -555. Your second example works because -555 (or 555-) can be divided by 2 i.e. -227.5 (or 227.5-).

You can see in the Excel UI where it allows the format as part of Text to Columns:

Also, you can set-up a number format to use trailing negative:

#,##0;#,##0-

See this blog-post.

Answer 2

From Microsoft:

IsNumeric returns True if the data type of Expression is Boolean, Byte, Decimal, Double, Integer, Long, SByte, Short, Single, UInteger, ULong, or UShort, or an Object that contains one of those numeric types. It also returns True if Expression is a Char or String that can be successfully converted to a number.

IsNumeric returns False if Expression is of data type Date or of data type Object and it does not contain a numeric type. IsNumeric returns False if Expression is a Char or String that cannot be converted to a number.