Printable View
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:would return 1234Code:Val("1234Excel")
would return 1234.5Code:Val("1234.5Excel")
would return 1234.5Code:Val("1234.5.56Excel")
would return 12342334Code:Val("1234 23 34")
would return 12323.56Code:Val("12323 .5 6")
Regards,
Transformer
Hi Transformer,
Very nice. Keep posting :)
Good!
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
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.Quote:
Originally Posted by Transformer
1. Val does not recognize the comma as a thousands delimiter. The following statement displays 1, not 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...Code:MsgBox Val("1,234.56")
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...Code:Number = "1,234.56"
MsgBox Replace(Number, ",", "")
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...Code:TextNumber = "12,34"
MsgBox Val(Replace(TextNumber, ",", "."))
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'...Code:MsgBox Val("12E3abc")
MsgBox Val("12e3abc")
MsgBox Val("12D3abc")
MsgBox Val("12d3abc")
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'...Code:MsgBox Val("&123")
MsgBox Val("&o123")
MsgBox Val("&O123") 'That is the letter "oh", not the digit zero
For a like reason, the following will display 3757...Code:MsgBox Val("&H123")
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...Code:MsgBox Val("&Head")
Code:' 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")
Another valuable input from our Guru. :cheers:
Thanks!;)Quote:
Originally Posted by Admin
Note to you and anyone subscribed to this thread... I just added a fourth item to the "gotcha" list in my previous message.
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!
Simple... just replace the space with a non-digit inside the Val function. For example...Quote:
Originally Posted by skittlz
Code:QuantityText = "156 2 mm spaces"
Quantity = Val(Replace(QuantityText, " ", "X")
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:Code:Val("1234.5.56Excel")
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.Quote:
Originally Posted by 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 So when I came across this thread it was good reinforcement with additional knowledge! Thanks again for explaining.