VBA Function: Val

The VBA Val function returns the numbers contained in a string until a non-numeric character is encountered.

This function only accepts . as the decimal separator.

Usage:

Val(text)

Example of Usage

Using the Val function to return different strings as numbers:

Sub example()
    
    MsgBox Val("1") 'Returns: 1
    MsgBox Val(" 1 ") 'Returns: 1
    MsgBox Val(1) 'Returns: 1
    
    MsgBox Val("1h") 'Returns: 1
    MsgBox Val("h1") 'Returns: 0

    MsgBox Val("1 number") 'Returns: 1
    MsgBox Val("number 1") 'Returns: 0
    
    MsgBox Val("2 2") 'Returns: 22
    MsgBox Val("2.2") 'Returns: 2.2
    MsgBox Val("2,2") 'Returns: 2
    
    MsgBox Val("123 Main Street") 'Returns: 123
    MsgBox Val("New York, NY 10001") 'Returns: 0
    
    MsgBox Val("Excel") 'Returns: 0
    
End Sub