Manipulating Strings
It is common for a string to be modified before it is displayed on a report. This can consist of simple reformatting or even joining the different elements of an array into a single string. Basic syntax has many functions for manipulating string data. Table 9-4 shows the functions for manipulating strings. Descriptions of each function are listed next to its name. The functions Filter(), Split(), and Picture() are different from .NET and are defined in more detail after the table.
Table 9-4. String Manipulation Functions
| Function Name | Description |
|---|---|
| Filter(str, find, include, compare) | Search an array of strings for a sub-string and return an array matching the criteria. |
| Replace(str, find, replace, start, | count, compare) |
| Find a string and replace it with another string. The parameters start, count and compare are all optional. | StrReverse(str) |
| Reverse the order of all characters in the string. | ReplicateString(str, copies) |
| Returns multiple copies of a string. | Space(val) |
| Returns the specified number of spaces as a single string. | Join(list, delimiter) |
| Join an array of strings into one string and separate them with the specified delimiter. | Split(str, delimiter, count, compare) |
| Split a single string into an array of strings based upon the specified delimiter. The parameters count and compare are optional. | Picture(str, template) |
| Formats the characters in a string onto a template. |
The Filter() function searches an array of strings for a matching sub-string. It returns an array of all the strings that have that sub-string in them. The first parameter is the string array and the second parameter is the sub-string to search for. Essentially, this function calls the InStr() function for every string in the array. If the InStr() doesn’t return a zero, then the string is added to the result array.
The Filter() function has an optional include parameter that tells the function to return an array of strings that don’t match the sub-string. Essentially, this would be the same as saying that it returns all strings where the InStr() function returns a zero. Pass the include parameter the value False to get an array of the strings that don’t have the sub-string in them. If you don’t pass a value for this parameter, then the default value of True is used. Listing 9-1 demonstrates using the Filter() function with different parameters.
‘Demonstrate the Filter() function
Dim StringArray() As String
Dim ResultArray() As String
StringArray = Array("abcd", "bcde", "cdef")
‘This will return an array with two elements: "abcd", "bcde"
ResultArray = Filter(StringArray, "bc")
‘This will return an array will return an array with one element: "cdef"
‘This is because it is the only element that doesn't have the sub-string
ResultArray = Filter(StringArray, "bc", False)
The Replace() function searches for a sub-string within another string, and if it finds it then it will replace it.
The Replace() function has two optional parameters that are important: start and count. The count parameter lets you limit how many string replacements are done. If you pass a number for this parameter then the number of replacements done cannot exceed that value. If you don’t pass a value for this parameter then all the sub-strings are replaced.
'Change the addresses so that they use abbreviations
Dim Streets As String
Streets = "123 Main Street, 456 Cherry Avenue, 999 Brook Street"
Streets = Replace(Streets, "Street", "St.")
Streets = Replace(Streets, "Avenue", "Ave.")
‘Streets is now "123 Main St., 456 Cherry Ave., 999 Brook St."
Formula = Streets
The Split() and Join() functions work together nicely. The Split() function takes a string and splits it into a string array. This makes it is easy to work on the individual strings. After you get done making any necessary changes to the individual strings you can combine them back into one string using the Join() function. How convenient!
This example demonstrates combining the functionality of the Split() and Join() functions. A string with the names of customers is available. We want the string to only have names with a prefix of “Mr.” This is done by splitting the names into an array of strings. Then the Filter() function is used to return an array with only the strings that match our criteria. This array is combined back into a comma-delimited string using the Join() function.
'Demonstrate the Split() and Join() functions
Dim Names As String
Dim NamesArray() as String
Names = "Mr. Jones, Sir Alfred, Ms. Bee, Mr. Smith"
NamesArray = Split(Names, ",")
'Get the names that only use Mr.
NamesArray = Filter(NamesArray,"Mr.")
'RJoin the array back into a comma-delimited string
Names = Join(NamesArray, ",")
'Names is now "Mr. Jones, Mr. Smith"
Formula = Names
The Picture() function maps a string onto a template. The first parameter is the source string and the second parameter is the template.
The template consists of a series of “x”s with other characters around it. Each character in the source string gets mapped onto each the “x”s in the template. The source string can use any character and it will get mapped. If the source string has more characters than what can fit in the template, then all remaining characters are added to the end. If the template has any non-“x” characters, then they stay as they are.
‘Demonstrate mapping a string with non-alphanumeric characters
Formula = Picture("ab&[{1234", "xxx..xx..x..")
‘The result is "ab&..[{..1..234"
This example illustrates that all characters in the source string were mapped onto the “x”s. It also shows that since the source string has nine characters and the template has six “x”s, then the extra three characters are added to the end.
