VAL returns the numeric value of a text string.  Text strings that contain a numeric value or a number with an MDL symbol (ie “<2”) will be converted to a number.  Will also return the Date Serial Number from a cell containing a date string.

SYNTAX:

VAL(Value, optional Default_Value)

Value: Text value or Cell Reference

Default_Value: Optional.  Displays optional default value when symbol has not been set up. Looks for first numerical value using a symbol (<,>,ND...ect) and displays the numerical value.  If set to -1, will return "" (i.e. blank) if the Value is a blank cell. 

EXAMPLES:

Formula	Returns	Comment
Val("4.7")	4.7
Val("<2")	2	Symbols are removed and the numeric part is converted to a number.
Val(C1)	4	Cell C1 contains the string “<4”
Val(VINFO(1,”Entry Min”))	15	VINFO is a text function that returns “15”, Val converts that to 15.0
Val("X",0)	0	Looks for first numerical value of symbol (<,>,ND). X is not a symbol that has been set up, therefore the default value (0) will be displayed.
Val("X")	-	Returns nothing since there is no default value and X is not a symbol or a number.
Val("ND")	0
Val(C2)	-	C2 is a blank cell.
Val(C2,0)	0	C2 is blank cell, therefore return specified default of zero.
Val(C2,-1)	-	Returns nothing based on the -1 default value.
VAL("100 mL")	100	Converts the numeric part until a non-numeric value is found.
VAL(" 123 Main, Hwy 74")	123	Spaces removed and value up to first non-numeric is converted.
VAL(IF(STR(D4)="PASS",1,0))	1	Returns 1 if D4 = "PASS" and the cell type will be numeric.  Notice VAL overides the STR (which tries to make the cell type text) because VAL is the outer most function.
STR(IF(VAL(B4)<10,"GOOD","BAD")	GOOD	Returns "GOOD" if the Value of B4 < 10.  Notice STR as the outer most function sets the cell type to text allowing GOOD (or BAD) to be displayed in the cell.
VAL("1/1/2021")	44197	The Date Serial Number of Jan 1st, 2021.

SEE ALSO: NFNP, Qualifier