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 instring
and:Right(
string, number
)returns the rightmost
number
characters instring
. 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 ofstring
, starting at character numberstart
. 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 ofStringToFind
withinStringToSearch
. IfStart
is missing, then the function starts searching at the beginning ofStringToSearch
. 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.