Val Function

[Rick Rothstein]

This function returns the numbers found in a string which is passed as an argument.
It stops reading the string at the first character that it can't recognize as part of a number.
But it dont stop on spaces.

I use Val quite a lot to pull a number off of the beginning of a text string, but there are a couple of possible "gotchas" that you have to watch out for.

1. Val does not recognize the comma as a thousands delimiter. The following statement displays 1, not 1,234.56...

MsgBox Val("1,234.56")

Of course, if you suspect there could be commas in your text string, just use the Replace function to remove them before applying the Val function...

Number = "1,234.56"
MsgBox Replace(Number, ",", "")

2. Val only recognizes the dot as a decimal point, so it your locale uses the comma for a decimal point, you will have to substitute a dot for commas before applying the Val function...

TextNumber = "12,34"
MsgBox Val(Replace(TextNumber, ",", "."))

3. Val recognizes E, e, D and d as "power of ten" delimiters, meaning, if the beginning of your text string starts with a number and one of these letters which, in turn, is followed by more numbers, those "more numbers" will be assumed to be a power of 10. All of the following statements will display 12000, not 12...

MsgBox Val("12E3abc")
MsgBox Val("12e3abc")
MsgBox Val("12D3abc")
MsgBox Val("12d3abc")

4. Val might not return 0 if the leading character is an ampersand (&) by itself or an ampersand followed by o, O, h or H depending on what follows. An ampersand by itself, &o or &O all signal VB to see if the digits that follow are less than 8. Each of these preceeding a series of digits from 0 to 7 tell VB treat the combination as an Octal number. So each of the following displays 83 because that is the decimal equivalent of 'Octal 123'...

MsgBox Val("&123")
MsgBox Val("&o123")
MsgBox Val("&O123")  'That is the letter "oh", not the digit zero

Okay, so you may have guessed by now that &h or &H preceeding the digits 0 to 9 and the letters a to f or the letters A to F will be interpretted as Hex numbers and the Val will generate the decimal equivalent for them. So, the following, for example, will display 291 because it is the decimal equivalent of 'Hex 123'...

MsgBox Val("&H123")

For a like reason, the following will display 3757...

MsgBox Val("&Head")

Okay, now for the "gotcha" for #4. (What, you thought the above was the gotcha?) Like with normal digits and non-digits, Val will read Octal and Hex digits (depending on the ampersand prefix) up until the first non-Octal and non-Hex digit it comes to (again, depending on the ampersand prefix). So, the following may not be expected results...

' These return 83, the decimal equivalent of 'Octal 123'
' The reason? Because the 8 following &O123 is a non-Octal digit.
MsgBox Val("&123823432543534434534")
MsgBox Val("&o123823432543534434534")
MsgBox Val("&O123823432543534434534")

' Similarly, these return 3757, the decimal equivalent
' of 'Hex ead' because the "M" and "m" are non-Hex digits
MsgBox Val("&HeadMan")
MsgBox Val("&headman")