MS-Excel / Functions and Formula

---

Using the Asc Function to Return a Character Code

The Asc function returns the character code for the first character of a string. Character codes are the numbers by which computers refer to letters. For example, the character code for a capital A is 65 and for a capital B is 66; a lowercase a is 97, and a lowercase b is 98.

The syntax for the Asc function is straightforward:

Asc(string)

Here, string is any string expression. For example, Asc("A") returns 65.

The following statements use the Asc function to return the character code for the first character of the current selection in the active document and display that code in a message box:

strThisCharacter = Asc(Selection.Text)
MsgBox strThisCharacter, vbOKOnly, "Character Code"

Using the Val Function to Extract a Number from the Start of a String

The Val function converts the numbers contained in a string into a numeric value. Val follows these rules:

• It reads only numbers in a string.
• It starts at the beginning of the string and reads only as far as the string contains characters that it recognizes as numbers.
• It ignores tabs, line feeds, and blank spaces.
• It recognizes the period as a decimal separator but not the comma.

This means that if you feed Val a string consisting of tabbed columns of numbers, such as the second line here, it will read them as a single number (in this case, 445634.994711):

Item#   Price   Available   On Order   Ordered
4456    34.99    4             7 	11

If, however, you feed it something containing a mix of numbers and letters, Val will read only the numbers and strings recognized as numeric expressions (for example, Val("4E5") returns 400000 because it reads the expression as exponentiation). For example, if fed the address shown in the next example, Val returns 8661, ignoring the other numbers in the string (because it stops at the N of Nick, the first character that isn't a number, a tab, a line feed, or a space):

8661 Nick Avenue Suite 4506, Oakland, CA 45710

The syntax for Val is straightforward:

Val(string)

Here, string is a required argument consisting of any string expression. The following statement uses Val to return the numeric variable StreetNumber from the string Address1:

StreetNumber = Val(Address1)