Use Val function to convert text values to numbers

Use Val function to convert text values to numbers

The Val() function can pull out any text (string) data from a field that has a mixture of both text and numbers, and return just the numeric values.

For example, in a field that stores mileage information field as, 30 miles but you want to pull out the numeric data, you would use Val (“fieldname”) like this:

Val (“mileage”)

The Val() function stops reading a string as soon as it comes across a character that it doesn’t recognize as a number so, symbols and characters such as dollar signs and commas, which are often considered as parts of numeric values are not recognized.

Note:  The only valid decimal separator recognized by the Val() function is the period (.). If you use a different decimal separator, as some international applications do, use the CDbl function instead.

Another way to use for the Val() function is when querying on two tables. For example, in Table 1 the ID number is stored as a text field and in Table 2 it is stored as a number field in the second table. In order to connect and query both tables, the data types needs to be similar. So, you’d use the VAL() function, to convert the ID field in Table 1 like this:

ConvertedID: Val([FieldName]) 

You can use the Val function in an expression. If you are not familiar with expressions, see how to create expressions or you can read more about adding functions to expressions.

For more information about functions see the help for built-in functions, properties, macro actions, and SQL keywords.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.