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, 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, the default value of True is used. Listing 9-1 demonstrates using the Filter() function with different parameters.
Listing 9-1. Using the Filter() function
‘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 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, 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, the number of replacements done cannot exceed that value. If you don’t pass a value for this parameter, 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 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 of 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 can fit in the template, all remaining characters are added to the end. If the template has any non-“x” characters, 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, the extra three characters are added to the end.
Question: I have a formula that concatenates strings together. I want each string to print on a new line. How do I force a string to start on the next line?
Answer: You can insert a carriage return and line feed before a string to print it on a new line. Here is an example in Basic syntax. Remember to replace the variables and field with the appropriate names from your report.
