• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

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


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 function

The syntax for this very useful function is:

InStr(Start, StringToSearch, StringToFind)

The return value is the position, starting 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 Str and Val functions

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


returns the string 123. 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 1234. Note, however, that Val does not recognize dollar signs or commas. Thus:


returns 0, not 12.00.

The Type Conversion functions

The Str and Val functions have been replaced by the more modern type conversion functions: CBool, CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CVar, and CStr. For instance, the function CStr converts its argument to a string, as in:


One advantage of the newer type conversion functions over the older Str and Val functions is that the new functions are international-aware. For instance, the CCur function converts an expression to currency format, taking into account the particular decimal separators, thousands separators, and other currency options that are determined by the locale setting of the computer upon which the function is being used.

The Trim , LTrim, and RTrim functions

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

Trim("  extra   ")

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:

                        sText = String(25, "B")

sets sText to a string consisting of 25 Bs. Also, 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:

                        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:

Option Compare Binary
Option Compare Text

one of which should be placed in the Declarations section of a module (in the same place as Option Explicit). 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 < À < . . . < Ø < à < . . . < ⊘

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 < À = à < B = b < . . . < Z = z < Ø = ⊘

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 by 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


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(string1, string2 [, compare])

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



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint