Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Val Function

  1. #1
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13

    Val Function

    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:
    Code:
    Val("1234Excel")
    would return 1234

    Code:
    Val("1234.5Excel")
    would return 1234.5

    Code:
    Val("1234.5.56Excel")
    would return 1234.5

    Code:
    Val("1234 23 34")
    would return 12342334

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

    Regards,
    Transformer
    Last edited by Admin; 06-11-2012 at 04:14 PM. Reason: edited a typo
    Regards,

    Transformer

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Transformer,

    Very nice. Keep posting
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    May 2012
    Posts
    1
    Rep Power
    0
    Good!

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Transformer View Post
    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...

    Code:
    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...

    Code:
    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...

    Code:
    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...

    Code:
    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'...

    Code:
    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'...

    Code:
    MsgBox Val("&H123")
    For a like reason, the following will display 3757...

    Code:
    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...

    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")
    Last edited by Rick Rothstein; 06-11-2012 at 10:46 PM.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Another valuable input from our Guru.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    Another valuable input from our Guru.
    Thanks!

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

  7. #7
    Junior Member
    Join Date
    Aug 2012
    Posts
    1
    Rep Power
    0
    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!

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by skittlz View Post
    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...

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

  9. #9
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    12

    Understanding Val()

    Code:
    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?

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by mrmmickle1 View Post
    Code:
    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.

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •