VAL Function in Excel VBA : To Extract Numeric Part from a String.

Posted: April 21, 2013 by Transformer in Excel, VBA
Tags: , , , ,

This function returns the numbers found at the beginning of 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 ignores spaces.

Syntax: VAL(string)

Examples:

Expression Result Comments
Val(“1234Excel5”) 1234 Stops reading at first alphabet
VAL(“1234.5Excel”) 1234.5 Dot (“.”) recognised as a decimal
VAL(“1234.5.56Excel”) 1234.5 VAL returns a value that is a valid number, so second dot not considered
VAL(“1234 23 34”) 12342334 Spaces ignored
VAL(“12323 .5 6”) 12323.56 Spaces ignored
VAL(“1,234.5”) 1 “,” not recognised as a thousands delimiter
VAL(“123E3Excel”) 123000 “E” recognized as “power of ten” delimiter
VAL(“123e3Excel”) 123000 “e” recognized as “power of ten” delimiter
VAL(“123D3Excel”) 123000 “D” recognized as “power of ten” delimiter
VAL(“123d3Excel”) 123000 “d” recognized as “power of ten” delimiter
Advertisements
Comments
  1. JohnS says:

    Where is the code Gyaan?

  2. RAK_da_Pira says:

    If the string contains no numbers, Val(string) returns 0. For example, Val(“Excel”) = 0.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s