Search code examples
vbaexcelisnumeric

VBA IsNumeric going WILD

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?

Solution

  • 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:

    enter image description here

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

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

    See this blog-post.