MS-Excel / Functions and Formula

---

Using Functions to Manipulate Strings

String variables are often useful for manipulating text. You can use them to store any quantity of text, from a character or two up to a large number of pages from a Word document or other text document. You can also use strings to store specialized information, such as filenames and folder names. Once you've stored the data in a string, you can manipulate it according to your needs.

Table below lists VBA's built-in functions for manipulating strings. Because some of these functions are more complex than other functions you've seen in the tutorial, and because they're frequently useful, there are detailed examples after the table.

VBA's string-manipulation functions

Function(Arguments)	Returns
InStr(start, string1, string2, compare)	A Variant/Long giving the position of the first instance of the search string (string2) inside the target string (string1), starting from the beginning of the target string
InStrRev(stringcheck, stringmatch, start, compare)	A Variant/Long giving the position of the first instance of the search string (stringmatch) inside the target string (stringcheck), starting from the end of the target string
LCase(string)	A String containing the lowercased string
Left(string, number)	A Variant/String containing the specified number of characters from the left end of string
Len(string)	A Long containing the number of characters in string
LTrim(string)	A Variant/String containing string with any leading spaces trimmed off it
Mid(string, start, length)	A Variant/String containing the specified number of characters from the specified starting point within string
Right(string, number)	A Variant/String containing the specified number of characters from the right end of string
RTrim(string)	A Variant/String containing string with any trailing spaces trimmed off it
Space(number)	A Variant/String containing number of spaces
StrComp(string1, string2, compare)	A Variant/Integer containing the result of comparing string1 and string2
StrConv(string, conversion, LCID)	A Variant/String containing string converted as specified by conversion for the (optional) specified Locale ID (LCID)
String(number, character)	A Variant/String containing number of instances of character
StrReverse(expression)	A String containing the characters of expression in reverse order
Trim(string)	A Variant/String containing string with any leading spaces or trailing spaces trimmed off it
UCase(string)	A String containing the uppercased string

Using the Left, Right, and Mid Functions to Return Part of a String

Frequently, you'll need to use only part of a string in your procedures. For example, you might want to take only the first three characters of the name of a city to create a code for a location. VBA provides several functions for returning from strings the characters you need:

• The Left function returns a specified number of characters from the left end of the string.
• The Right function returns a specified number of characters from the right end of the string.
• The Mid function returns a specified number of characters starting from a specified location inside a string.

USING THE LEFT FUNCTION

The Left function returns the specified number of characters from the left end of a string. The syntax for the Left function is as follows:

Left(string, length)

Here, the string argument is any string expression - that is, any expression that returns a sequence of contiguous characters. Left returns Null if string contains no data. The length argument is a numeric expression specifying the number of characters to return. length can be a straightforward number (such as 4, or 7, or 11) or it can be an expression that results in a number. For example, if the length of a word were stored in the variable named LenWord and you wanted to return two characters fewer than LenWord, you could specify the expression LenWord - 2 as the length argument; to return three characters more than LenWord, you could specify LenWord + 3 as the length argument.

One way to use the Left function would be to separate the area code from a telephone number that was provided as an unseparated 10-digit number from a database. In the following statements, the telephone number is stored in the String variable strPhone, which the code assumes was created earlier:

Dim strArea As String
strArea = Left(strPhone, 3)

These statements create the variable Area and fill it with the leftmost three characters of the variable strPhone.

USING THE RIGHT FUNCTION

The Right function is the mirror image of the Left function. Right returns a specified number of characters from the right end of a string. The syntax for the Right function is as follows:

Right(string, length)

Again, the string argument is any string expression, and length is a numeric expression specifying the number of characters to return. And, again, Right returns Null if string contains no data, and length can be a number or an expression that results in a number.

To continue the previous example, you could use the Right function to separate the last seven digits of the phone number stored in the string strPhone from the area code:

Dim strLocalNumber As String
strLocalNumber = Right(strPhone, 7)

These statements create the variable strLocalNumber and fill it with the rightmost seven characters from the variable strPhone.

USING THE MID FUNCTION

The Left and Right functions extract a substring from the left or right side of a string. The Mid function fetches a substring out of the middle of a string.

The Mid function returns the specified number of characters from inside the given string. You specify a starting position in the string and the number of characters (to the right of the starting position) that you want extracted.

The syntax for the Mid function is as follows:

Mid(string, start[, length])

Here are the elements of the syntax:

• As in Left and Right, the string argument is any string expression. Mid returns Null if string contains no data.
• start is a numeric value specifying the character position in string at which to start the length selection. If start is larger than the number of characters in string, VBA returns a zero-length string. In code, an empty string is typed as two quotation marks with nothing inside: strState = "".
• length is an optional numeric expression specifying the number of characters to return. If you omit length or use a length argument greater than the number of characters in string, VBA returns all the characters from the start position to the end of string. length can be an ordinary literal number or an expression that results in a number.

Using the phone-number example, you could employ Mid to pluck the local exchange code out from within a 10-digit phone number (for instance, extract the 555 from 5105551212), like this:

Dim strPhone As String

strPhone = "5105551212"
MsgBox Mid(strPhone, 4, 3)

This statement displays three characters in the variable strPhone, starting at the fourth character.

You can also use Mid to find the location of a character within a string. In the following snippet, the Do Until... Loop walks backward through the string strFilename (which contains the FullName property of the template attached to the active document in Word) until it reaches the first backslash (\), storing the resulting character position in the Integer variable intLen. The message box then displays that part of strFilename to the right of the backslash (determined by subtracting intLen from the length of strFilename) - the name of the attached template without its path:

Dim strFilename As String, intLen As Integer
strFilename = ActiveDocument.AttachedTemplate.FullName
MsgBox strFilename

intLen = Len(strFilename)
Do Until Mid(strFilename, intLen, 1) = "\"
    intLen = intLen - 1
Loop
MsgBox Right(strFilename, Len(strFilename) - intLen)

This example is more illustrative than realistic for two reasons: First, you can get the name of the template more easily by just using the Name property rather than the FullName property. Second, there's a function called InStrRev (discussed next) that returns the position of one string within another by walking backward through it.