Q11329 - INFO: VAL

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.