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
|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.
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:
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:
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.
In this tutorial:
- Using Built-in Functions
- What Is a Function?
- Passing Arguments to a Function
- Using Functions to Convert Data from One Type to Another
- Using the Asc Function to Return a Character Code
- Using the Str Function to Convert a Value to a String
- Using the Format Function to Format an Expression
- Using the Chr Function and Constants to Enter Special Characters in a String
- Using Functions to Manipulate Strings
- Using InStr and InStrRev to Find a String within Another String
- Using LTrim, RTrim, and Trim to Trim Spaces from a String
- Using Len to Check the Length of a String
- Using the StrComp Function to Compare Apples to Apples
- Using VBA's Mathematical Functions
- Excel VBA's Date and Time Functions
- Using the DateDiff Function to Return an Interval
- Using File-Management Functions