Creating Formulas with Character Functions

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Create formulas using character functions

Character String Functions

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.

Functions in Formulas and Variables

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.

Variables vs Formulas Comparison

VariablesFormulas
Can be used in different blocks or reports in the documentOnly for use in a single cell
Can be identified as an objectN/A

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.

Character Extraction Using the Right() Function

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.

Syntax

Returns the rightmost characters of a string.

Code snippet
string Right(string ; num_chars)
Copy code

Characters Extracted Using the SubStr() Function

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.

Syntax

Returns part of a string.

Code snippet
string Substr(string ; start; length)
Copy code

Code snippet
=SubStr([Store name] ; 11 ; Length([Store name]))
Copy code

String Replacement Using the Replace() Function

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

Syntax

Replaces part of a string with another string.

string Replace(replace_in ; replaced_string ; replace_with)

Character Position Using the Pos() Function

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:

Code snippet
=Pos([Address] ; " ") 
Copy code

Create a variable Address that contains the syntax:

Code snippet
=SubStr([Address] ; [Space] + 1 ; Length([Address])) 
Copy code

Syntax

Code snippet
int Pos(test_string;pattern[;start][;end]])
Copy code

Save progress to your learning plan by logging in or creating an account

Login or Register