Page 143 - Excel Progamming Weekend Crash Course

h540629 ch09.qxd 9/2/03 9:34 AM Page 118

118 Saturday Morning

String is the original string, and length is the number of characters to extract. If
length is more than the length of string, the entire string is returned.
As with most of VBA’s string manipulation functions, the original string is
never modified by Left or Right functions. The return value of the function
Note is a new string with the specified modifications.

The Mid Function
The Mid function extracts characters from the middle of a string. The syntax is:

Mid(string, start, length)
String is the string to extract from, and start is the character position at which to
start extracting. Length specifies the number of characters to extract. The length argument
is optional; if it is omitted, all characters from start to the end of string are returned. If
start is greater than the length of string, an empty string is returned. The following are
some examples of using the Mid function:

Mid(“Bill Gates”, 4, 5) ‘ Returns “l Gat”
Mid(“Bill Gates”, 4) ‘ Returns “l Gates”
Mid(“Bill Gates”, 12) ‘ Returns “” (an empty string)

The Mid Statement
The Mid statement replaces part of one string with another string. The syntax is:

Mid(string1, start, length) = string2

String1. The string to modify.
Start. The character position in string1 at which to start.
Length. The number of characters in string1 to be replaced. This argument is
optional; if omitted, the number of characters replaced is equal to the length of
string2.
String2. The string to insert into string1.
All or part of string2 will be inserted into string1. The length of string1 always
remains unchanged.
Be aware that the Mid statement is distinct from the Mid function, which
does something totally different. The Mid function returns part of a string
Note without changing the original string, while the Mid statement modifies a
string. Note also that the Mid statement is the only one of VBA’s string
manipulation tools that modifies the original string.