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


Val( string )

The required stringargument is any valid string expression.


The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized. However, the function recognizes the radix prefixes &O (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed characters are stripped from the argument.

The following returns the value 1615198:

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

In the code below, Val returns the decimal value -1 for the hexadecimal value shown:


Note:  The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.

Query example



SELECT DateofSale,val(DateofSale) AS testVal FROM ProductSales;

Return the values from "DateofSale" and leading numeric characters from the values of field "DateofSale" in the column testVal. Val() will stop reading the string at the first non-numeric character.

VBA example

Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

This example uses the Val function to return the numbers contained in a string.

Dim MyValue
MyValue = Val("2457") ' Returns 2457.
MyValue = Val(" 2 45 7") ' Returns 2457.
MyValue = Val("24 and 57") ' Returns 24.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!