Text functions

Examples

Example of default comparison behavior

In this example, Time displays on columns. On rows, there are three line items. The first two line items show the top sales performer for All Regions and North. The third line item contains a formula that uses COMPARE to see if the top sales performer in the North region is the same as in all regions.

The formula uses the default behavior for the Locale argument, which is the ‘en’ ISO 369 language code.

In Jan 21, Peter was the text value for both All Regions and North. As they're identical, COMPARE returns a value of 0.

In Feb 21, Rashid was the text value for All Regions, and Peter the text value for North. As the first letter of both Rashid and Peter is different, the COMPARE function compares these characters. R comes after P in alphabetical order, so the text value Rashid is considered greater and the formula returns a value of 1.

In Mar 21, Jenny was the text value for all regions, and John the text value for North. The first letter of both Jenny and John, J, is the same, so it's ignored in the comparison. This means the second letter of each text value is compared. As e comes before o in alphabetical order, the formula considers the text value John greater, and returns a value of -1.

Example of PRIMARY comparison

If you use the PRIMARY keyword for the Comparison mode argument, the COMPARE function compares the two text values for any differences in base characters.

Example of SECONDARY comparison

If you use the SECONDARY keyword for the Comparison mode argument, the COMPARE function:

1. Compares the two text values for any differences in base characters.
2. If there's no difference in base characters, compares the accents in the two text values.

Example of TERTIARY comparison

If you use the TERTIARY keyword for the Comparison mode argument, the COMPARE function:

1. Compares the two text values for any differences in base characters.
2. If there's no difference in base characters, compares the accents in the two text values.
3. If there's no difference in base characters or accents, compares the case of the two text values.

Example of all Comparison mode keywords

This example shows how the result the COMPARE returns changes based on the Comparison mode argument.

Example of Locale argument

When used for the Locale argument, some codes change how the COMPARE function treats characters. For example, if you use the Swedish language code, sv, the letters w and v are considered to be the same base character.

Examples

General example of FIND

In this example, a module has a Companies list on columns. There are two line items on rows. The first contain several company names. The second contains a formula that uses the FIND function.

In this example, the FIND function looks for the text string ABC, starting from the 4th character. It returns a result of 0 for the text string Imperial abc ltd because the string abc is not capitalized. It returns a result of 0 for the text string ABC ltd because ABC is within the first four characters.

Examples

General examples

In this example, a Clothing list displays on columns. It contains four items, Hats, Shirts, Shorts, and Trousers.

Three text-formatted line items display on rows, Red Product, Blue Product, and Yellow Product. These contain product names. There is also one list-formatted line item, Clothing List, which contains product types. These line items contain the values used for the Text argument.

The bottom four rows contain formulas that use the LEFT function to extract characters from the left of the text strings.

Use LEFT with items from list dimension

You can use the ITEM function to return the list-formatted column names. Then, you can use the NAME function to convert the names to be text-formatted, which is compatible with the LEFT function.

In this example, the Fruit list displays on columns, and two line items display on rows. Both line items contain formulas.

In this example, 5 is used for the the Number of characters argument, so the formula returns the first five letters of the list-formatted Products strings.

Example with text entered directly into formula

LEFT("Favorite Team", 8) 

This example uses the LEFT function with a text string typed directly into the formula. It uses 8 for the Number of characters argument. This means it returns Favorite, the first eight characters of the text string.

Examples

In this example, a module contains information about profit for each month. Time displays on columns, and line items on rows.

The text-formatted Profit Commentary line item contains manually entered text about the profit each month. The number-formatted Commentary Length line item uses the LENGTH function to return the number of characters in Profit Commentary as a number. The Boolean line item Within character limit for reports? then uses the result of Commentary Length and calculates if it's under 70.

Examples

General example of LOWER

This example has a Territory list on columns, and two line items on rows. The first line item contains the product categories sold in each region. The second line item contains a formula that uses LOWER to make the product categories lowercase, for use in another system.

Example of LOWER with international characters

In this example, a module contains a list of languages on columns. The are two line items on rows, one that contains international characters, and another that uses the LOWER function to make them lowercase. 

The LOWER function works with most languages without the Locale argument. However, it has no effect on certain languages, such as Chinese, Japanese, or Korean, as these languages do not have uppercase or lowercase characters.

Example of the Locale argument

If you use the LOWER function with a text string that contains an uppercase I and use the tr_tr code for the Locale argument, the result contains a lowercase i without a dot, ı.

LOWER("I", tr)

The result of this formula is ı.

Examples

General example

In this example, the module has a User roles list on columns, which contains the different categories of users to send emails to. The module also has text-formatted line items on rows. The line items contain the email addresses to send the emails to, the content of the emails, and a formula that uses the MAILTO function.

You can publish the links generated by the MAILTO function to a dashboard or page. Use this to enable users to send emails more easily.

Formula result for Model Builders list item

When a user clicks the text Send email to model builder within the Link to send email line item, the MAILTO function generates an email where:

• Brianna.builder@anaplan.com is the recipient in the To field of the email.
• The Subject line reads New update to the Reporting Model.
• The body text of the email reads: Hello Brianna, there's been an update to the reporting model. Please check it and update the model with your required actions.

Formula result for Reviewers list item

When a user clicks the text Send email to reviewers within the Link to send email line item, the MAILTO function generates an email where:

• Renata.reviewer@anaplan.com is the recipient in the To field of the email.
• Sawyer.security@anaplan.com and Patrice.planner@anaplan.com are the recipients in the Cc field of the email.
• Alex.admin@anaplan.com is the recipient in the Bcc field of the email.
• The Subject line of the email reads Review the latest update to the Reporting Model.
• The body text of the email reads: Hello All, Please review the changes that have been made to the Reporting Model. Provide any feedback via the agreed channels.

Examples

This example contains a Languages list on columns, and three line items on rows. Two of the line items contain the data for the Display text and URL arguments and one contains a formula that uses this information.

The result of the formula in the Link to landing pages line item is a clickable link that goes to the respective landing page. You can publish a line item that uses the MAKELINK formula to a dashboard or worksheet to help users access links.

Examples

Simple example of MID function

In this example, the Location code line item contains an eight character code. Each part of the code is two characters long and separated by a hyphen. The first and second characters indicate the city, the fourth and fifth characters the region, and the seventh and eighth characters the country.

In order to extract the region code, this example uses 4 for the Start position argument, and 2 for the Number of characters argument. As a result, the formula extracts two characters starting from the fourth character, which is the region code. This can then be used in other parts of the model or exported.

Use MID with list items via NAME function

You can use the NAME function to convert a list item to text so that you can use the MID function with it. You can also use ITEM function to return a list item that you can then use with the NAME function.

This example uses the same data as the previous example, but the Location code line item is list-formatted instead. As a result, it must be converted to text-formatted in order to use the MID function with it. 

Extract text from the end of a string

You can use the MID function in combination with the LENGTH function to extract characters a set number of characters from the end of a string. This can be useful if the starting characters of a text string are variable but the ending characters are consistent.

To do this:

1. Provide a text-formatted line item, text constant, or general expression for the Text argument.
2. Use an expression enclosed in parentheses for the Start position argument.
   • Use LENGTH to determine the number of characters in each value of the Text argument.
   • Use a negative number to count back the number of characters from the end of the string to extract text from.
3. For the Number of characters argument, specify the number of characters you want to extract.

In this example, the Product Code line item contains a code. The code describes the wine type, an identifying number, and a region number. As the words used for wine type are of varying length, the identifying number is not a set number of characters from the start of the Product Code line item.

The bottom line item in this table contains a formula that demonstrates the above procedure. This means that it extracts a string a set number of characters from the end of the product code.

Examples

Use NAME with Time

This example shows how you can use the NAME function to return a text formatted line item from the Time list. 

The module is displaying the months for 2020.

The NAME calculation function is applied to the line item Month Period formatted as Month and returns the Period Name Month as a line item formatted as text:

Source model

Period Name Month: NAME(Month Period)

Accounts period

Use NAME with another function

You can use NAME with another function. This example shows how you can use NAME to return the name of a product in text format.

Source model

Product Margins

The values returned in the Product Name line item have the text data type. 

Examples

Extract first word from line items

In this example, a Clothing list displays on columns, with four items, Hats, Shirts, Shorts, and Trousers.

Three text-formatted line items display on rows, Red Product, Blue Product, and Yellow Product. These contain product names. There's also one list-formatted line item, Clothing List, which contains product types. These line items contain the values used for the Text argument.

The bottom four rows contain formulas that use the RIGHT function to extract characters from the right of the text strings.

Use RIGHT with items from a list dimension

You can use the ITEM function to return the list-formatted column names. Then, you can use the NAME function to convert the names to be text-formatted, which is compatible with the RIGHT function.

In this example, the Fruit list displays on columns, and two line items display on rows. Both line items contain formulas.

Example with text entered directly into formula

RIGHT("Favorite Team", 4) 

This example uses the RIGHT function with a text string typed directly into the formula. It uses 4 for the Number of characters argument. This means it returns Team, the last four characters of the text string.

Examples

General example of SUBSTITUTE

You can enclose text in double quotation marks to enter literal text values directly into the SUBSTITUTE function. In this example, each argument is enclosed in double quotation marks. The following formula:

SUBSTITUTE(“ababababa”, “aba”, “c”)

searches the text ababababa for any instances of aba and replaces them with c. This means it returns a text value of cbcba.

SUBSTITUTE with text from a line item

This example uses data from three modules.

The first module, Email Templates, contains only text line items. Each of these line items contain the phrases [Region Placeholder] and [Revenue Placeholder], which are the text to be substituted using the SUBSTITUTE function.

Email Templates

The second module, Profit and loss summary, contains the Cities list on columns. On rows, there are line items that contain a variety of financial results.

Profit and loss summary

The third module, Revenue Email, has the Cities list on columns, and line items on rows. The line items contain two formulas that use data from the Email templates and Profit and loss modules to create text that changes based on the data within the modules.

Revenue Email

The second formula uses the result of the first formula for the Text to search argument. This is because each usage of the SUBSTITUTE function can search only for a single text value. In order to replace two different strings, you need to use the SUBSTITUTE function twice.

Replace underscores with spaces

You can use SUBSTITUTE to replace underscores, or other characters, in text with spaces. To do so, you must use a space enclosed in double quotation marks for the Replacement text argument. A line item cannot contain only a space, so this is the only method you can use.

SUBSTITUTE("Text_with_underscores", "_", " ")

The result of this formula would be Text with underscores, with the underscores replaced with spaces.

Examples

TEXT(Revenue)

In this example, Revenue is a numeric line item.

The formula converts the values of the Revenue line item to be text values. As a result, the line item contains numbers as text and is available for use in text functions such as LEFT and RIGHT.

Examples

General example

This example uses two modules. The first module, Sales Transactions, has two line items on columns that contain the customer name and the value of transactions. The Transactions list is on rows. The Regions list is a page dimension, and London is currently selected.

Sales Transactions

Region: London

The second module, List of customers, has three line items on columns. The Regions list is on rows. The London row corresponds to the example above.

The first two line items contain formulas that show a list of customers by region. The formula in the List of all customers line item uses the ALL keyword for the Duplicate behavior keyword, so it returns duplicated text values multiple times. The formula in the List of all customers without duplicates line item uses the UNIQUE keyword for the Duplicate behavior keyword,  so it does not return duplicated text values.

List of customers

Examples

In this example, the Office locations line item contains the Las Vegas, London, and Melbourne values. The formula removes all irregular spaces from each value, and replaces extra spaces between words with single space characters.

Examples

General example of UPPER

This example has a Territory list on columns, and two line items on rows. The first line item contains the product categories sold in each region. The second line item contains a formula that uses UPPER to make the product categories uppercase, for use in another system.

Example of UPPER with international characters

In this example, a module contains a list of languages on columns. The are two line items on rows, one that contains international characters, and another one that uses the UPPER function to make them uppercase.

The UPPER function works with most languages without the Locale argument. However, it has no effect on certain languages, such as Chinese, Japanese, or Korean, as these languages do not have uppercase or lowercase characters.

Example of the Locale argument

If you use the UPPER function with a text string that contains a lowercase i and use the tr_tr code for the Locale argument, the result contains an uppercase I with a dot, İ.

UPPER("i", tr)

The result of this formula is İ.