Creating Formulas with Character Functions

The following character string functions are used in this lesson:

Right() and Left() functions are only useful for manipulating constant strings or a constant number of characters. The SubStr() function is a more powerful function. Use the SubStr() function to extract strings where the length and position of the specified strings are not consistent.

You use the functions of character strings to create a formula. You create a formula by typing it in the Formula field or in the Formula Editor. Either method calculates the data and displays the resulting values in the column that you have highlighted in the block.

You can also drag and drop objects in the Formula field and complete the formula by typing in the Formula field.

The table shows a comparison of the use of variables and formulas:

If in doubt, always create a variable instead of a formula because there are certain things that can be done with variables that cannot be done with formulas.

The Year object in the eFashion.unx universe is formatted to return the year as a four-digit value (for example, 2017). However, to display the year in a two-digit format (for example, 17), use the Right() character string function to display the last two characters.

The Right() function displays a fixed number of characters, starting from the right of the character string, and removes the others.

The character string function Left() works in the same way, except that the function displays the specified number of characters starting from the left.

The example syntax can be broken down into the following parts:

Section Header Modification

Modify the section header to include the Year name and modify it to display two digits using the following syntax:

=NameOf([Year])+" "+Right([Year];2)

The example syntax =NameOf([Year])+" "+Right([Year];2) can be broken down into the following elements:

Syntax	Description
NameOf([Year])	To display the name of the Year object.
+" "	To concatenate a space after the object name.
+ Right([Year]);2)	To concatenate and display two characters starting from the right end of the character string.

The Store name object in the eFashion.unx universe returns the name of each store in the company eFashion. However, each store name is prefixed by "e-Fashion".

Suppose that you want to remove the "e-Fashion" portion of the character string. Formatting the report to remove the "e-Fashion" prefix is a challenge because the length of each store name is different, with or without the "e-Fashion" portion. Neither the Right() or Left() functions works here.

You can remove the "e-Fashion" prefix by using the SubStr() function.

The SubStr() function extracts a specified character string from within a string.

=SubStr([Store name];11;Length([Store name]))

The example syntax can be broken down into the following parts:

Using the SubStr() Function to Modify the Display

If you replace the Store name column with the Store column, the report displays the store names without the e-Fashion prefix.

In the example, we want to substitute the letter "Q" for the word "Quarter".

The Replace() function will modify the character string displayed.

You use the following syntax:

=Replace([Quarter];"Q";"Quarter ")

The example syntax can be broken down into the following parts:

If you have a report on quarterly revenue that you want to format to show the word "Quarter" instead of the letter "Q", use the Replace() function to modify the character string.

Using the Replace() Function to Modify the Display

The figure, Quarterly Revenue, shows the resulting report when you replace the Quarter column with the new variable.

You are working on a report that contains the Store and Address columns.

In the Address column, you want to display only the street names for each of the eFashion stores. The Address object from the universe returns both the building number and the street name. The number of spaces before the street name begins is inconsistent. Therefore, you must create a substring to return only the portion of the address that is needed.

Notice that in each address, there is a space after the building number. If you can, create a formula that recognizes the position of the space for each entry. Use that position as a starting point for the SubStr() function.

Create a variable Space that contains the syntax:

=Pos([Address];" ")

Create a variable Address that contains the syntax:

=SubStr([Address];[Space]+1;Length([Address]))

The example syntax can be broken down into the following parts:

Using the Pos() Function

The Space variable locates the first space within each address string. If you add a Space column to the report, the result displays the number of characters before the street name for each address, as shown in the figure, Using the Pos Function to Divide the Address Object.

The integer that the Space variable returns corresponds to the number of characters before the street address begins. You can now create a variable called Address that removes the building number from the address string.

To finish the report, delete the Space column and replace the Address column with the Address variable that you created.