Posts Tagged ‘Number’

Format() Function for Strings : VBA

Posted: July 2, 2013 by Theodoulus in Excel, VBA
Tags: , , , ,

Many times we encounter with such a situation when we have a “String” and from this string we need to fetch a value in fromat of  Date, Percentage, Currency, Fixed, Scientific, Yes/No, On/Off etc.

Here comes the most used function of VBA, The “Format() Function”.


Expression is the String which you pass in order to fetch the desired output. Format is the optional argument. If you don’t pass Format then you will get the same String which you passed as input.
Although, Excel has it’s own formats for the data but You can define your own [Format] too. Here is the list of the Defined Formats in the Excel 2007:

General Number     -This gives you the same string as you passed as input
Fixed              -Atleast one digit on the left and two digits after the decimal place
Currency           -With Thousand Separators along with 2 decimal places
Standard           -Thousand separator, atleast one digit at the left of decimal place and two digit at
                    the right the decimal place
Percent            -Displays the percent value with two digits at the right of decimal place
True/False         -True for zero and False for Non-Zero
On/Off             -OFF for Zero and On for non Zero
Yes/No             -Displays No for zero and Yes for non-zero


  Format("1234","General Number")            'returns 1234
  Format("1234","currency")                  'returns $1,234.00
  Format("1234","Fixed")                     'returns 1234.00
  Format("1234","Standard")                  'returns 1,234.00
  Format("1234","Percent")                   'returns 123400.00%
  Format("1234","Yes/No")                    'returns   Yes
  Format("1234","General Date")              'returns 5/18/1903
  Format("1234","Long Date")                 'returns Monday, May 18, 1903             
  Format("1234","MMM, DDDD YYYY")            'returns May, Monday 1903

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)


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