Val Function

[Transformer]

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.

Syntax: Val(string)

Example:
Val("1234Excel") would return 1234


Val("1234.5Excel") would return 1234.5


Val("1234.5.56Excel") would return 1234.5


Val("1234 23 34") would return 12342334


Val("12323 .5 6") would return 12323.56

Regards,
Transformer

[Admin]

Hi Transformer,

Very nice. Keep posting :)

[skmall]

Good!

[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?:biglaugh:) 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")

[Admin]

Another valuable input from our Guru. :cheers:

[Rick Rothstein]

Another valuable input from our Guru. :cheers:
Thanks!;)

Note to you and anyone subscribed to this thread... I just added a fourth item to the "gotcha" list in my previous message.

[skittlz]

Hi Everyone,

Very useful post. I'm having a few issues with the val function, and I'm hoping one of you guys can help. As you said, val function does not recognize spaces between numbers. A piece of my code is currently set up to find a value of a cell. However, the cell contains additional numbers which I do not want - for example, a cell contains "156 2 mm spaces" - I just want the val function to give me the quantity (156). I want the val function to recognize space as a non-numeric character.

Please help. Thanks!

[Rick Rothstein]

Hi Everyone,

Very useful post. I'm having a few issues with the val function, and I'm hoping one of you guys can help. As you said, val function does not recognize spaces between numbers. A piece of my code is currently set up to find a value of a cell. However, the cell contains additional numbers which I do not want - for example, a cell contains "156 2 mm spaces" - I just want the val function to give me the quantity (156). I want the val function to recognize space as a non-numeric character.

Simple... just replace the space with a non-digit inside the Val function. For example...


QuantityText = "156 2 mm spaces"
Quantity = Val(Replace(QuantityText, " ", "X")

[mrmmickle1]

Val("1234.5.56Excel")


I am new to vba and i have been looking at some string items today. Regarding the above code I have a question.... Why does this code only return:

1234.5?

If this is to return numbers why would it not return

1234.5.56?

Is it because the 56Excel is alphanumeric? Does Val() not recognize numbers that are alphanumeric?

[Rick Rothstein]

Val("1234.5.56Excel")


I am new to vba and i have been looking at some string items today. Regarding the above code I have a question.... Why does this code only return:

1234.5?

If this is to return numbers why would it not return

1234.5.56?

Is it because the 56Excel is alphanumeric? Does Val() not recognize numbers that are alphanumeric?

Val returns the leading number from a text string... it stops looking at the first character that no longer can be used to form a real number. Since numbers can only have one decimal point, it rejects the second decimal point because it cannot be used to form a real number, so Val returns everything before that invalid numeric character.

[mrmmickle1]

Rick,

This makes sense. Now that I understand this I will continue on to lower responses. I just wanted to make sure that I was fully grasping the concept before I moved on to something else. I was looking at this website a little earlier in the day: VBA Excel: Strings (http://www.functionx.com/vbaexcel/topics/strings.htm) So when I came across this thread it was good reinforcement with additional knowledge! Thanks again for explaining.