IsNumeric gives unexpected results in excel/vba for different regional settings

I had the impression that IsNumeric(MyString) would reveal if Val(MyString) is expected to fail or not. I have found unexpected differences depending on regional settings.

Example 1, Swedish regional settings (uses , as decimal separator):

• IsNumeric("1.1")=> True
• Val("1.1")=> 1.1
• IsNumeric("1,1")=> True
• Val("1,1")=> 1

Example 2, Estonian regional settings (also uses , as decimal separator):

• IsNumeric("1.1")=> False
• Val("1.1")=> 1.1
• IsNumeric("1,1")=> True
• Val("1,1")=> 1

My typical generic code for converting as string into a number is:

Function ConvertToNumber(MyNumber as String) as Double 
    If IsNumeric(MyNumber) then
        ConvertToNumber = Val(Replace(MyNumber, ",", "."))
    Else
        MsgBox "Invalid format!"
    End If
End Function

But this failed unexpectedly in Estonian regional settings. Any idea if this is the intended behavior? Microsoft explanations on what IsNumber is doing is also a bit vague. What do you suggest to use instead? If Val(MyNumber & "1")<>0? This would fail for some special cases such as 0E+0. One could also consider catching any error from:

CDbl(Replace(MyString, ".", Application.International(xlDecimalSeparator))

I could use regular expressions, but there must be better ways.

I would appreciate input on this.

/Jonas

The problem is using the Val function which does

Returns the numbers contained in a string as a numeric value of appropriate type.

So it does not what you expet, because it does not convert a string into numbers but extract numbers contained in a string until the first non-numeric character that is not a whitespace.

That means

Val("    1615 198th Street N.E.")

will return 1615198 as double.

So what Val interprets as a decimal is always the . in all localizations. That means it will always cosider Val("1.1") as a number but in Val("1,1") the comma is the first non-numberic, non-whitespace character so it stops there and returns 1 only.

What you were looking for is the CDbl which actually converts a string into a number using the decimal seperator of your system.

Option Explicit

Function ConvertToNumber(ByVal MyNumber As String) As Double
    Dim RegionalNumber As String
    RegionalNumber = MyNumber
    
    ' if you want the user to be able to enter dots as well as commas
    ' make sure all dots and commas are converted to the decimal seperator of your system
    ' otherwise go without the conversion
    RegionalNumber = Replace$(RegionalNumber, ".", Application.DecimalSeparator)
    RegionalNumber = Replace$(RegionalNumber, ",", Application.DecimalSeparator)
    
    If IsNumeric(RegionalNumber) Then
        ConvertToNumber = CDbl(RegionalNumber)
    Else
        MsgBox "Invalid format!"
    End If
End Function

If you test this with

Sub test() 
    Debug.Print ConvertToNumber("1.1")
    Debug.Print ConvertToNumber("1,1")
End Sub

on a system where comma is the decimal seperator it should both times return 1,1 as a number.

---

Explanation why your tests returned the results they returned

Example 1, Swedish regional settings (uses ',' as decimal separator):

• IsNumeric("1.1")=> True
  Because . is considered as date seperator so it is a valid number
• Val("1.1")=> 1.1
  Because val always considers dot as decimal separator
• IsNumeric("1,1")=> True
  Because , is considered as decimal seperator
• Val("1,1")=> 1 Because val always considers comma as non-numeric character

Example 2, Estonian regional settings (also uses ',' as decimal separator):

• IsNumeric("1.1")=> False
  Because dot in estonian is not considered date seperator this is not a valid number
• Val("1.1")=> 1.1
  Because val always considers dot as decimal separator
• IsNumeric("1,1")=> True
  Because , is considered as decimal seperator
• Val("1,1")=> 1 Because val always considers comma as non-numeric character

Note that IsNumeric accepts thousand seperators, date seperators and decimal seperators as well as currency symbols.