When creating a report, you often want to embed the information from several string fields together as one field. For example, a customer’s first and last name fields can be combined to be one complete name field. In this way, formatting and moving this single field around the report is easy.
By using formulas to manipulate the data contained in string fields you can get exactly what you need from your database.
String Manipulation Formulas
String manipulation formulas include:
Subscript
Concatenation
Left
Mid
Right
Subscript
When you want to use only a certain part of a string field, you can extract characters from the string field using the subscript function.
For example, you want to use only a first initial of a contact name instead of the entire name, or you want to extract several characters from a string field. In the first example, you would create the formula:
Returns "A", where the Contact First Name is Anne-Marie.
When extracting several characters from a string field, you would create the formula:
Returns "Anne", where the Contact First Name is Anne-Marie
Concatenation
When you want to join two or more components together to form a singular continuous string, you would use one of these concatenation symbols:
&
Enables you to join fields of different data types.
+
Enables you to join string fields.
For example, if you want to add descriptive text to your data field, you would write the formula:
The Left Function
Left (str, length)
The Left function consists of a text string (str) and length and obtains a certain number of characters from the left end of a text string. For instance, you could use the Left function to obtain just the area code from the values in a field containing phone numbers. You would write the formula:
This formula would retrieve the first three numbers from the phone number, which is typically the area code.
The Mid Function
The Mid function returns a specified number of characters from a string. The second argument is the character position where the part to be taken begins. The optional third argument is the length of the string that you want to be taken out. If the third argument is not specified, everything from the start position to the end of the string is extracted. Use this function when you must extract a set of characters from somewhere in the middle of a text string. For example, you must extract the middle two characters of a field as an identifier for customers.
This formula returns a two–character substring from the inside of the string starting with the third character position. In the case of Alley Cat Bikes, the identifier is "le".
The Right Function
The Right function extracts the given number of text characters from the right side of the specified string. Use this function to obtain just the right part of the string. For example, you must obtain the last four digits of social security numbers stored in a field as an employee identifier.
This formula returns the last four digits of the social security number as a string.
