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:

Mid("Library.doc",9,3)

returns the string “doc”. If the length parameter is missing, as in:

Mid("Library.doc",9)

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

Get Writing Word Macros, Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.