VBA String Functions

Here are a handful of useful functions that apply to strings (both constants and variables):

The Len function

The Len function returns the length of a string—that is, the number of characters in the string. Thus, the code:

Len("January Invoice")

returns the number 15 .

The UCase and LCase functions

These functions return an all uppercase or all lowercase version of the string argument. The syntax is:

UCase(string) LCase(string)

For instance:

MsgBox UCase("Donna") will display the string DONNA. The Left, Right, and Mid functions

These functions return a portion of a string. In particular:

Left(string, number)

returns the leftmost number characters in string, and:

Right(string, number)

returns the rightmost number characters in string. For instance:

MsgBox Right("Donna Smith", 5) displays the string Smith. The syntax for Mid is:

Mid(string, start, length)

This function returns the first length number of characters of string, starting at character number start. For instance:


returns the string xls. If the length parameter is missing, as in:


the function will return the rest of the string, starting at start. The InStr, InStrRev functions

The syntax for the very useful InStr function is:

Instr(Start, StringToSearch, StringToFind)

The return value is the position, beginning at Start, of the first occurrence of StringToFind within StringToSearch. If Start is missing, then the function starts searching at the beginning of StringToSearch. For instance:

MsgBox Instr(1, "Donna Smith", "Smith")

displays the number 7, because "Smith" begins at the seventh position in the string "Donna Smith."

The InStrRev function is analogous to InStr but searches backwards through the StringToSearch string.

The Replace function

This very useful function (not available in Excel 97) is used to replace a substring in a string by another string. For instance, the code:

MsgBox Replace("the car is red", "red", "blue")

displays the string "the car is blue".

The Str and Val functions

The Str function converts a number to a string. For instance:


returns the string 12 3. Conversely, the Val function converts a string that represents a number into a number (so that we can do arithmetic with it, for instance). For example:

returns the number 4.5 and:

Val("1234 Main Street")

returns the number 12 34. Note, however, that Val does not recognize dollar signs or commas. Thus:

The Trim, LTrim, andRTrim functions

The LTrim function removes leading spaces from a string. Similarly, RTrim removes trailing spaces, and Trim removes both leading and trailing spaces. Thus:

returns the string extra.

The String and Space functions

The String function provides a way to quickly create a string that consists of a single character repeated a number of times. For instance:

sets sText to a string consisting of 25 Bs. The Space function returns a string consisting of a given number of spaces. For instance:

sText = Space(25)

sets sText to a string consisting of 25 spaces.

The Like operator and StrCmp function

The Like operator is very useful for comparing two strings. Of course, we can use the equal sign, as in:

string1 = string2

which is true when the two strings are identical. However, Like will also make a case-insensitive comparison or allow the use of pattern matching. The expression:

string Like pattern returns True if string fits pattern, and False otherwise. (Actually, the expression can also return Null.) We will describe pattern in a moment.

The type of string comparison that the Like operator uses depends upon the setting of the Option Compare statement. There are two possibilities, one of which should be placed in the Declarations section of a module (in the same place as Option Explicit):

Option Compare Binary Option Compare Text

Note that the default is Option Compare Binary.

Under Option Compare Binary, string comparison is in the order given by the ANSI character code, as shown here:

A < B < . . . < Z < a < b < . . . < z < A < . . . < 0 < a < . . . < 0

Under Option Compare Text, string comparison is based on a case-insensitive sort order (determined by your PC's locale setting). This gives a sort order as shown here:

A = a < A = a < B = b < . . . < Z = z < 0 = 0

By the way, the last item in the Text sort order is the "]" character, with ANSI value 91. This is useful to know if you want to place an item last in alphabetical order—just surround it with square brackets.

The pattern-matching features of the Like operator allow the use of wildcard characters, character lists, or character ranges. For example:

Matches any single character

Matches zero or more characters

matches any single digit (0-9)


Matches any single character in charlist [!charlist]

Matches any single character not in charlist For more details, check the VBA help file.

The StrCmp function also compares two strings. Its syntax is:

StrComp(stringl, string2 [, compare])

and it returns a value indicating whether stringl is equal to, greater than, or less than string2. For more details, check the VBA help file.

0 0

Post a comment