Excel: Val Function (VBA only)

In Excel, the Val function accepts a string as input and returns the numbers found in that string.

The syntax for the Val function is:

Val( string )

string is a string expression.

Note:

The Val function will stop reading the string once it encounters the first non-numeric character. This does not include spaces.

Applies To:

Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Val("10 Main Street") would return 10

Val("34 10 Main Street") would return 3410

Val(" 34 10 Main Street") would return 3410

Val(" 34 - 10 Main Street") would return 34

Val("075") would return 75

VBA Code

The Val function can only be used in VBA code. For example:

Dim LNumber As Double

LNumber = Val("56.2 tables")

In this example, the variable called LNumber would now contain the value of 56.2.