Anaplan provides a range of functions that enable you to work with text.

Text-formatted values enable you to collaborate with and capture insights from people who use the models you build. Use the text functions available in Anaplan to effectively work with this data.

You can use text functions to extract strings from text, find specific characters, concatenate text values, convert values to text, and more. See the full list of text functions in the table below.

TitleSyntaxDescription
FINDFIND(Text to find, Text to search [, Starting character])

The FIND function searches for the first occurrence of a text value within another one. If the text contains the specified characters, the function returns a number. This number indicates the position of the first occurrence of the text value searched for.

LEFTLEFT(Text [, Number of characters]) 

Extracts a string of characters from text, starting from the left.

LENGTHLENGTH(Text to evaluate)

The LENGTH (or LEN) function returns the number of characters in a text string.

LOWERLOWER(Text [, Locale])

The LOWER function converts text values to lowercase.

MAILTOMAILTO(Display text, To [, CC] [, BCC] [, Subject] [, Body text])

Use the MAILTO function to generate clickable links that send an email. You can specify recipients, subjects, and body text.

MAKELINKMAKELINK(Display text, URL)

The MAKELINK function generates clickable links in a module.

MIDMID(Text, Start position [, Number of characters])

Extracts a number of characters from a text string, starting from a character you select.

NAMENAME(List item)

Use the NAME function to convert data from a list item to text.

RIGHTRIGHT(Text [, Number of characters])

Extracts a string of characters from text, starting from the right. 

SUBSTITUTESUBSTITUTE(Text to search in, Text to find, Replacement text)

The SUBSTITUTE function finds all occurrences of a text value within another one, and replaces them with a given value.

TEXTTEXT(Number to convert)

The TEXT function converts numeric values to text.

TEXTLISTTEXTLIST(Text to concatenate, Separator, List to reference [, Duplicate behavior])

The TEXTLIST function concatenates a series of text values into a single text value.

TRIMTRIM(Text)

The TRIM function removes all leading and trailing spaces, and extra spaces between words in a text string.

UPPERUPPER(Text [, Locale])

The UPPER function converts text values to uppercase.

COMPARE

You could use COMPARE as the basis of conditional formatting for identical text values.

Syntax

COMPARE(Text to compare 1, Text to compare 2 [, Comparison mode] [, Locale])

Arguments

ArgumentData typeDescription
Text to compare 1 (required)TextThe first text value to compare.
Text to compare 2 (required)TextThe second text value to compare.
Comparison modeKeyword

The comparison mode to use.

The available keywords are IDENTICAL, PRIMARY, SECONDARY, and TERTIARY.

There's more information in the Comparison mode argument keywords section below.

LocaleKeyword

The locale to use. This only affects certain languages.

The available locales are based on ISO 639 and ISO 3166 codes.

The COMPARE function returns a number.

Comparison mode argument keywords

For more information about how to determine if 

KeywordDescription
IDENTICALCompares the Text to compare 1 and Text to compare 2 arguments to check if they're identical in all regards.
PRIMARY

Compares the base characters of the Text to compare 1 and Text to compare 2 arguments.

The alphabetical order of text values is compared from left to right, in line with the locale chosen.

If the starting characters of two text values are the same, but one contains more characters, the string with more characters is greater.

SECONDARY

Compares the base characters and accents of the Text to compare 1 and Text to compare 2 arguments.

If the base characters of two text values are the same, COMPARE considers the one that contains accents as greater.

TERTIARY

The default keyword if you omit the Comparison mode argument.

Compares the base characters, accents, and case of the Text to compare 1 and Text to compare 2 arguments.

If the base characters and accents of two text values are the same, but one contains uppercase characters, COMPARE considers the one that contains uppercase characters as greater.

Syntax example

COMPARE(Top Salesperson North, Top Salesperson South)

Additional information

How text values are compared

Whether a text value is considered greater than or less than another text value is based on several criteria, similar to collation in Unicode or Java.

The Comparison mode keywords compare the base characters, accents, and case of text values respectively. However, this behavior varies depending upon the locale chosen.

When you use the SECONDARY or TERTIARY keywords to compare the accents or case of text values, the COMPARE function also applies prior criteria. For example, if you use TERTIARY to compare the case of text values, it first compares the base characters and accents.

Effect of Locale argument

The Locale argument accepts ISO language codes that correspond to the locales available in Java 8. You can use either:

  • Two letter ISO 639 language codes such as en, fr, de, or tr.
  • Four letter codes made of both ISO 639 language codes and ISO 3166 country codes such as en-us, fr-be, de-at, or tr-tr. However you must replace the hyphen (-) with an underscore (_) in Anaplan, so these become en_us, fr_be, de_at, and tr_tr respectively.

The Locale argument only has an effect on certain languages. For example, Turkish, which is represented by the tr code. In Turkish, there are four versions of the character i. Lowercase and uppercase versions, both with and without a dot.

The Locale argument of the COMPARE function may alter the fundamental properties of a character, such as its position in the roman alphabet. Here are some examples of how the Locale argument can affect the COMPARE function:

  • With the English en language code, the Danish character ø is treated as if it's the base character of o. With the Danish da language code, the base character of ø is not considered to be an accented character, and it comes after z in alphabetical order.
  • In Swedish, represented by the sv language code, the characters v and w are considered to be the same.
  • In Lithuanian, represented by the lt language code, the character y precedes j in alphabetical order.

Excel equivalent

EXACT

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.


Jan 21Feb 21Mar 21
Top Sales All RegionsPeterRashidJenny
Top Sales NorthPeterPeterJohn

Top Sales in North?

COMPARE(Top Sales All Regions, Top Sales North)

01-1

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

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

In Feb 21Rashid 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 21Jenny 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.


Jan 21Feb 21Mar 21
Chosen Employee 1TimTimTim
Chosen Employee 2TimTomTam
COMPARE(Chosen Employee 1, Chosen Employee 2, PRIMARY)0-11

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.

Jan 21Feb 21Mar 21
Chosen Employee 1SorenSorenSøren
Chosen Employee 2SorenSørenSoren
COMPARE(Chosen Employee 1, Chosen Employee 2, SECONDARY)0-11

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.

Jan 21Feb 21Mar 21
Chosen Employee 1FrankfrankFrank
Chosen Employee 2FrankFrankfrank
COMPARE(Chosen Employee 1, Chosen Employee 2, TERTIARY)0-11

Example of all Comparison mode keywords

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


Primary differenceSecondary differenceTertiary difference
Text 1báÁ
Text 2aaá
COMPARE('Text 1', 'Text 2', PRIMARY)100
COMPARE('Text 1', 'Text 2', SECONDARY)110
COMPARE('Text 1', 'Text 2', TERTIARY)111

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.


Region
EnglishSweden
SwedishSveden
COMPARE(English, Swedish, PRIMARY)1
COMPARE(English, Swedish, PRIMARY, sv)0
FIND

For example, you can use FIND to identify items that contain a specific product name.

Syntax

FIND(Text to find, Text to search [, Starting character])

Arguments

ArgumentData typeDescription
Text to find (required)TextThe text value to locate in the Text to search argument.
Text to search (required)TextThe text value to search within for an instance of the Text to find argument.
Starting positionNumber

The position to start the search from. Includes spaces.

Does not affect the numeric result the FIND function returns.

The FIND function returns a numeric result.

Syntax example

FIND("Ltd.", Companies) 

Additional information

Search behavior

If the FIND function does not find an instance for the Text to find argument, it returns a value of 0.

The FIND function is case-sensitive. This means that if the Text to search argument contains the Text to find argument with different capitalization, the FIND function returns a value of 0.

The FIND function considers spaces to be a character like letters or numbers.

Constraints

The FIND function only looks for exact text matches. It is not possible to use search patterns such as regular expressions or wildcards.

If you use emoji with the FIND function, the result of the function can be incorrect.

Calculation engine functionality differences

In Polaris, the FIND function considers the length of all Unicode characters to be one. This means that the FIND function returns the correct starting character.

In the Classic Engine, the FIND function considers the length of:

  • Unicode characters from within the Basic Multilingual Plane (BMP) to be 1
  • Unicode characters from outside the BMP to be 2
  • The length of composite characters (such as those with accents or diacritic marks) to be equal to the number of components

Excel equivalent

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.


Company 1Company 2Company 3Company 4
Company NameImperial ABC ltdImperial abc ltdABC ltdCompany ABC

Character that ABC starts at

FIND("ABC", Company Name, 4)

10009

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.

LEFT

For example, if a product SKU is prefixed with a code of fixed length, you can use the LEFT function to extract this from the rest of the text.

Syntax

LEFT(Text [, Number of characters]) 

Arguments

ArgumentData typeDescription
Text

Text

Can be a line item, text constant, or general expression.

The text to extract characters from.
Number of characters (optional)

Number

Can be a line item, property, or expression.

The number of characters to extract from the string.

If you omit this argument, the LEFT function returns only the first, left, character from the text string.

If you use a negative number or zero for this argument, the LEFT function returns a blank result.

If this argument is greater than the number of characters in the Text argument, the LEFT function returns only the characters in the string.

The LEFT function returns a text-formatted result.

Syntax example

LEFT(Product SKU, 3)

In this example, a line item named Product SKU contains a list of strings that identify different products. Each SKU starts with a three character string that describes the product category.

This formula uses the Product SKU line item for the Text argument and 3 for the Number of characters argument. As a result, the formula returns the first three characters for each product SKU as a text-formatted result.

Constraints

The LEFT function cannot be used with list items. Convert list items to text-formatted with the NAME function if you need to use the LEFT function with them.

Calculation engine functionality differences

In Polaris, the LEFT function behaves as expected with all text values, including those that contain composite characters or characters from outside the Basic Multilingual Plane (BMP).

In the Classic Engine, the LEFT function considers the length of:

  • Unicode characters from within the BMP to be 1
  • Unicode characters from outside the BMP to be 2
  • The length of composite characters (such as those with accents or diacritic marks) to be equal to the number of components

Excel equivalent

Examples

General examples

In this example, a Clothing list displays on columns. It contains four items, HatsShirtsShorts, 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.


HatsShirtsShortsTrousers
Red ProductRed HatsRed ShirtsRed ShortsRed Trousers
Blue ProductBlue HatsBlue ShirtsBlue ShortsBlue Trousers
Yellow ProductYellow HatsYellow ShirtsYellow ShortsYellow Trousers
Clothing ListHatsShirtsShortsTrousers
LEFT(Red Product, 3)RedRedRedRed
LEFT(Blue Product, 7)Blue HaBlue ShBlue ShBlue Tr
LEFT(Yellow Product, 10)Yellow HatYellow ShiYellow ShoYellow Tro
LEFT(NAME(Clothing List), 3)HatShiShoTro

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.


ApplesPeachesBananasPearsCarrotsCucumbersLettuce
ITEM(Products)ApplesPeachesBananasPearsCarrotsCucumbersLettuce
LEFT(NAME(ITEM(Products)), 5)ApplePeachBananPearsCarroCucumLettu

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.

LENGTH

For example, you can use LENGTH to determine if a string is within the character limit for another system.

Syntax

LENGTH(Text to evaluate)

Arguments

ArgumentData typeDescription
Text to evaluateTextThe text to evaluate the number of characters within. The LENGTH function returns the number of characters as a number.

The LENGTH function returns a number.

Syntax example

LENGTH(Customer comments)

In this example, the LENGTH function returns the number of characters for each value of the text-formatted Customer comments line item.

Additional information

Behavior with spaces and blank values

When the Text to evaluate argument contains whitespace characters, each of these are counted as a single character by the LENGTH function.

If a value for the Text to evaluate argument has no characters, the LENGTH function returns a value of zero.

The LEN function

Instead of LENGTH, you can use LEN. The LEN function is functionally identical to the LENGTH function except for its name.

Constraints

For some unicode symbols outside the Basic Multilingual Plane the LENGTH function returns 2 for a single character. For example, text values that are encoded as a combination of two characters (a base character and accent), or emoji.

Calculation engine functionality differences

In Polaris, the LENGTH function behaves as expected with all text values, including those that contain composite characters or characters from outside the Basic Multilingual Plane (BMP).

In the Classic Engine, the LENGTH function considers the length of:

  • Unicode characters from within the BMP to be 1
  • Unicode characters from outside the BNP to be 2
  • The length of composite characters (such as those with accents or diacritic marks) to be equal to the number of components

Excel equivalent

Examples


Jan 21Feb 21Mar 21
Profit CommentaryThe profit in January was slightly below target, possibly due to the supply chain issue.The profit in February was on target.The profit in March surpassed the target due to the new campaign.

Commentary Length

LENGTH(Profit Commentary)

883765

Within character limit for reports?

Commentary Length < 70


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.

LOWER

Syntax

LOWER(Text [, Locale])

Arguments

ArgumentData typeDescription
Text (required)TextThe text to make lowercase.
LocaleKeyword

The locale to use. Only has an effect with certain languages.

The available locales are based on ISO 639 and ISO 3166 codes.

The LOWER function returns a text value.

Syntax example

LOWER(Customer comments)

Additional information

Effect of Locale argument

The Locale argument accepts ISO language codes that correspond to the locales available in Java 8. You can use either:

  • Two letter ISO 639 language codes such as en, fr, de, or tr.
  • Four letter codes made of both ISO 639 language codes and ISO 3166 country codes such as en-us, fr-be, de-at, or tr-tr. However you must replace the hyphen (-) with an underscore (_) in Anaplan, so these become en_us, fr_be, de_at, and tr_tr respectively.

The Locale argument only has an effect on certain languages. For example, Turkish, which is represented by the tr code. In Turkish, there are four versions of the character i. Lowercase and uppercase versions, both with and without a dot.

Calculation engine functionality differences

In Polaris, you cannot use the Locale argument with the LOWER function.

In the Classic Engine, you can use the Locale argument.

Excel equivalent

LOWER

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.


New YorkSan FranciscoLondonYorkTokyoOsaka
Product categoriesAccessories, Footwear, Innerwear, Outerwear, Sports EquipmentAccessories, Footwear, Sports equipment, Accessories, Footwear, Innerwear, Outerwear, Sports EquipmentOuterwear, Sports EquipmentAccessories, Footwear, Innerwear, Outerwear, Sports EquipmentAccessories, Outerwear, Sports Equipment

Lowercase product categories

LOWER(Product categories)

accessories, footwear, innerwear, outerwear, sports equipmentaccessories, footwear, sports equipment,accessories, footwear, innerwear, outerwear,  sports equipmentouterwear, sports equipmentaccessories, footwear, innerwear, outerwear, sports equipmentaccessories, outerwear, sports equipment

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. 


Accented characterCyrillicGreekChineseJapaneseKorean
Uppercase characterÅЖΨ

Lowercase character

LOWER(Uppercase character)

åжψ

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 ı.

MAILTO

You could use the MAILTO function to automatically create links that notify model users of a change to data or a required action.

Syntax

MAILTO(Display text, To [, CC] [, BCC] [, Subject] [, Body text])

Arguments

ArgumentData typeDescription
Display text (required)TextThe text to use as a clickable link to send an email. Displays in the result line item.
To (required)Text

The recipients within the To field of the email that the function sends.

For multiple recipients, email addresses should be separated by commas.

CcText

The recipients within the Cc field of the email that the function sends.

For multiple recipients, email addresses should be separated by commas.

BccText

The recipients within the Bcc field of the email the function sends.

For multiple recipients, email addresses should be separated by commas.

SubjectTextThe subject of the email that the function sends.
Body textTextThe body text of the email that the function sends.

The MAILTO function returns a text result that you can click to send an email.

Syntax example

MAILTO("Click to send email", "", "", Mail list, Email subject, Content)

This example uses line items named Mail list, Email subject, and Content. This means that the recipient, subject, and content of the email are easy to change by clicking different links.

Additional information

Different types of text-formatted line items

The arguments for the MAILTO function work with all text-formatted line items regardless of whether they have the General, Email, or Link text type.

Omit optional arguments

The only required arguments for the MAILTO function are Display text and To. To omit any of the other arguments, such as Cc or Bcc, use two double quotation marks for the argument. This provides a blank text entry for them, which enables you to provide later arguments.

Add line breaks to Body text argument

You can add line breaks or paragraphs to the Body text argument if it refers to a line item (you cannot add line breaks directly into a formula). These also display in the email that the MAILTO function generates. To add a line break when you edit a text-formatted line item, press:

  • Ctrl + Alt + Enter on Windows
  • Ctrl + Option + Enter on Mac OS

Constraints

Default email application for operating system

When you use the MAILTO function to send an email, it opens a new email window in the default email application selected for your operating system. The email pre-populates with the information provided in the arguments of the MAILTO function. To ensure the MAILTO function works properly, select the correct email application for your operating system and browser.

Maximum email length

The MAILTO function sends the details of an email to your default email application via a URL string. 

Different browsers support different maximum lengths for URL strings. Internet Explorer supports 2,048 characters, and other browsers such as Google Chrome, Firefox, Safari, and Microsoft Edge support about 80,000. However, the MAILTO function is limited by a combination of the operating system, browser, and email application. For example, a combination of Windows operating system, Google Chrome browser, and Outlook email application limits characters to about 2,000. 

If the combined character count of the recipient's email addresses, the email subject, and the body text cause the URL to exceed the maximum length for your browser, the MAILTO function does not work.

If your email contains non-ASCII characters such as Japanese or Cyrillic, each character displays in URLs as multiple symbols and numbers, usually about 3 to 6. This is based on percent-encoding. Additionally, in percent-encoding, a whitespace character and various punctuation display as three characters. This means that the maximum length of MAILTO emails composed of non-ASCII characters is can be about one sixth to one third as long as standard ASCII characters. Additionally, the maximum length of MAILTO emails that contain a lot of punctuation is also shorter.

Calculation engine functionality differences

You cannot currently use the MAILTO function in Polaris. 

In the Classic Engine, you can. 

Excel equivalent

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.


Model BuildersReviewers
Email linkSend email to model builderSend email to reviewers
ToBrianna.builder@anaplan.comRenata.reviewer@anaplan.com
Cc
Sawyer.security@anaplan.com, Patrice.planner@anaplan.com
Bcc
Alex.admin@anaplan.com
SubjectNew update to the Reporting ModelReview the latest update to the Reporting Model
Body text

Hello Brianna,

There's been an update to the Reporting Model. Please check it and update the model with your required actions.

Hello All,

Please review the changes that have been made to the Reporting Model. Provide any feedback via the agreed channels.

Link to send email

MAILTO(Email link, To, Cc, Bcc, Subject, Body text)

Send email to model builderSend email to reviewers

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.
MAKELINK

For example, if a module contains information about products you sell, you can create links to the online store page for those products.

Syntax

MAKELINK(Display text, URL)

Arguments

ArgumentData typeDescription
Display textTextThe text that displays for the link. When a user clicks this text, they're taken to the link specified in the URL argument.
URLTextThe URL for the link.

The result of the MAKELINK function must be a text format line item with a text Type of Link, otherwise the link is not clickable

Syntax example

MAKELINK("Click here to view the expenses board", "https://us2a.app.anaplan.com/a/apps/app/2b12f4a4-aba0-4033-9be6-bbc77d86812d/boards/8b30567a-7edd-4c69-aeee-1b6b4ae8dc24")

In this example, the text Click here to view the expenses board is a clickable link. The link goes to an Anaplan board specified in the URL argument.

Constraints

  • The MAKELINK function only works with valid HTTP (http://) or HTTPS (https://) URLs.

Calculation engine functionality differences

You cannot currently use the MAKELINK function in Polaris. 

In the Classic Engine, you can. 

Excel equivalent

ENCODEURL

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.


EnglishFrenchGermanJapaneseSpanish
Link titleEnglish landing pageFrench landing pageGerman landing pageJapanese landing pageSpanish landing page
Landing page URLhttps://www.anaplan.com/https://www.anaplan.com/fr/https://www.anaplan.com/de/https://www.anaplan.com/jp/https://www.anaplan.com/es/

Link to landing pages

MAKELINK(Link title, Landing page URL)

English landing pageFrench landing pageGerman landing pageJapanese landing pageSpanish landing page

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.

MID

For example, if a product SKU is prefixed with a code of fixed length, you can use the MID function to extract the characters after the code.

Syntax

MID(Text, Start position [, Number of characters])

Arguments

ArgumentData typeDescription
Text (required)

Text

Can be a line item, text constant, or general expression.

The text to extract characters from.
Start position (required)

Number

Can be a line item, property, or expression.

The numeric starting position in the text string. The extracted characters include the character in the start position you select.

If you use a Start position that is not present in the text string, or a negative number, the function returns a blank result.

Number of characters

Number

Can be a line item, property, or expression.

The number of characters to extract from the text string.

If you omit this argument, the MID function returns the character in the position specified in the Start position argument.

If you use a negative number or zero for this argument, the MID function returns a blank result.

If this argument is greater than the number of characters in the Text argument, the MID  function returns only the characters in the string.

The MID function returns a text-formatted result.

Syntax example

MID(Product SKU, 4, 10)

In this example, a line item named Product SKU contains a list of strings that identify different products. Each SKU starts with a three character string that describes the product category. After this, the product SKU contains a unique string of between five and 10 characters in length that identifies each product.

This formula uses the Product SKU line item for the Text argument, 4 for the Start position argument, and 10 for the Number of characters argument. As a result, The formula returns the unique string that starts from the fourth character of the product SKU. The formula returns up to 10 characters for each unique string, which includes all characters for those with less than 10.

Constraints

The MID function cannot be used with list items. Convert list items to text-formatted with the NAME function if you need to use the MID function with them.

Calculation engine functionality differences

In Polaris, the MID function behaves as expected with all text values, including those that contain composite characters or characters from outside the Basic Multilingual Plane (BMP).

In the Classic Engine, the MID function considers the length of:

  • Unicode characters from within the BMP to be 1
  • Unicode characters from outside the BNP to be 2
  • The length of composite characters (such as those with accents or diacritic marks) to be equal to the number of components

Excel equivalent

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.


SapporoHakodateTokyoHachiōjiKyotoUjiNahaOkinawa
Location code01-01-JP02-01-JP01-13-JP02-13-JP01-26-JP 01-26-JP01-47-JP02-47-JP
MID(Location code, 4, 2)0101131326264747

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. 


SapporoHakodateTokyoHachiōjiKyotoUjiNahaOkinawa
Location code01-01-JP02-01-JP01-13-JP02-13-JP01-26-JP 01-26-JP01-47-JP02-47-JP
MID(NAME(Location code), 4, 2)0101131326264747

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.


Sauvignon BlancChiantiRieslingMerlotPinot NoirZinfandel
Product CodeWhite-101-FRWhite-102-BRWhite-103-AURed-201-ESRed-202-ARRed-203-NZ
MID(Product Code, (LENGTH(Product Code) -6), 3)101102103201202203
NAME

Syntax

NAME(List item)

Arguments

ArgumentData typeDescription
List itemListThe list item to convert to text.

The NAME function returns a text result.

Syntax example

NAME (Month period)

This returns the month period as a text string where month period is the line item and the result is the name of the month.

Excel Equivalent 

T

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:

FormulaDescriptionResult
NAME(Month Period)

The module, Accounts Period, displays the Key Dates and Expiry Dates for a company's accounts.

The line items are:

Key Dates

Expiry Dates

The line items and their formats are:

Period Name Month (text format)

Month Period (time format; Month)
where the month periods are editable cells.

The formula NAME(Month Period) identifies the Month for the Month Period the line item. It then converts the data type from a list item formatted line item to a text.

The result displays in the Key Dates and Expiry Dates columns for the Period Name Month line item.

Source model

Period Name Month: NAME(Month Period)

Accounts period


Key DatesExpiry Dates

Period Name Month

NAME(Month Period)

Feb 20Apr 20
Month PeriodFeb 20Apr 20

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.

FormulaDescriptionResult
NAME(ITEM(Products))

In this example, a company is looking at the Product Margins for their clothing products. 

The module is called Product Margins. The list is called Products and is on columns. Line items displays on rows.

The function, NAME references the line item, Product Name., which is list-formatted with the Products list.

The formula uses two functions; ITEM and NAME.

The ITEM function returns the list item of the line item, then the NAME function converts that list item to text.

Source model

Product Margins


T-shirtsDressesSweatersTrousersShorts
Annual Purchase328,765238,976348,432349,876467,831
Annual Sales404,876289,743401,239378,765502,349
Product SeasonSummerSummerFallFallSummer
Rank clothing with same season46463
Annual Margin76,11150,76752,80728,88934,518

Product Name

NAME(ITEM(Products))

T-shirtsDressesSweatersTrousersShorts

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

RIGHT

For example, if an automatically generated string ends with numbers that represent a date, you can use the RIGHT function to extract the date.

Syntax

RIGHT(Text [, Number of characters])

Arguments

ArgumentData typeDescription
Text

Text

Can be a line item, text constant, or general expression.

The text to extract characters from.
Number of characters (optional)

Number

Can be a line item, property, or expression.

The number of characters to extract from the text string.

If you omit this argument, the RIGHT function returns only the last, right, character from the text string.

If you use a negative number or zero for this argument, the RIGHT function returns a blank result.

If this argument is greater than the number of characters in the Text argument, the RIGHT function returns only the characters in the string.

The RIGHT function returns a text-formatted result.

Syntax example

RIGHT(Feedback ID, 10)

In this example, each piece of feedback Anaplan receives from another system has a unique code that identifies it. This ends with a 10 character string that corresponds to the date in the format dd-mm-yyyy.

This formula uses the Feedback ID line item for the Text argument and 10 for the Number of characters argument. As a result, this formula extracts the 10 characters that represent the date from the end of each feedback ID. This can then be used in other parts of the model or exported.

Constraints

The RIGHT function cannot be used with list items. Convert list items to text-formatted with the NAME function if you need to use the RIGHT function with them.

Calculation engine functionality differences

In Polaris, the RIGHT function behaves as expected with all text values, including those that contain composite characters or characters from outside the Basic Multilingual Plane (BMP).

In the Classic Engine, the RIGHT function considers the length of:

  • Unicode characters from within the BMP to be 1
  • Unicode characters from outside the BMP to be 2
  • The length of composite characters (such as those with accents or diacritic marks) to be equal to the number of components

Excel equivalent

Examples

Extract first word from line items

In this example, a Clothing list displays on columns, with four items, HatsShirtsShorts, 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.


HatsShirtsShortsTrousers
Red ProductRed HatsRed ShirtsRed ShortsRed Trousers
Blue ProductBlue HatsBlue ShirtsBlue ShortsBlue Trousers
Yellow ProductYellow HatsYellow ShirtsYellow ShortsYellow Trousers
Clothing ListHatsShirtsShortsTrousers
RIGHT(Red Product, 4)Hatsirtsortssers
RIGHT(Blue Product, 6)e HatsShirtsShortsousers
RIGHT(Yellow Product, 8)low Hatsw Shirtsw ShortsTrousers
RIGHT(NAME(Clothing List), 3)atsrtsrtsers

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.


ApplesPeachesBananasPearsCarrotsCucumbersLettuce
ITEM(Products)ApplesPeachesBananasPearsCarrotsCucumbersLettuce
RIGHT(NAME(ITEM(Products)), 5)pplesachesnanasPearsrrotsmbersttuce

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.

SUBSTITUTE

For example, you can use the SUBSTITUTE function to change the content of a text string based on its dimensionality, such as region.

Syntax

SUBSTITUTE(Text to search in, Text to find, Replacement text)

Arguments

ArgumentData typeDescription
Text to searchTextThe text to search for instances of the Text to replace argument.
Text to replaceTextThe text value to substitute with Replacement text within Text to search in. Each instance of the text value is replaced.
Replacement textTextThe text value to replace the Text to replace argument with.

The SUBSTITUTE argument returns a text value.

Syntax example

SUBSTITUTE( Email content, "[Region placeholder]", Region name)

Additional Information

Search behavior

The SUBSTITUTE function only looks for exact text matches. It is not possible to use search patterns such as regular expressions or wildcards.

Substitution behavior

If the Text to search in value contains multiple instances of the Text to replace value, the SUBSTITUTE function replaces each of them. The replacement is in the order that the Text to replace values display, from left to right. However, it is not recursive. This means that if the Replacement text argument contains the text value from the Text to replace argument, the result is not substituted also.

If the Text to search in value does not contain the Text to replace value, the SUBSTITUTE function returns the Text to replace value unchanged. This means that if a blank value is used for the Text to replace argument, the SUBSTITUTE function has no effect.

Effect on different character types

The SUBSTITUTE function is case sensitive. It also works with emoji and other characters that are not part of the Unicode Basic Multilingual Plane.

The SUBSTITUTE function does not consider canonically equivalent characters to be the same character. For example, U+00E2 (Latin Small Letter A with Circumflex), which renders as â. This is not equivalent to the combination of U+0061 (Latin Small Letter A) and U+0302 (Combining Circumflex Accent), which also renders as â.

Calculation engine functionality differences

In Polaris, the SUBSTITUTE function does not match the base characters of a composite characters. For example, the SUBSTITUTE function does not consider â to contain a or が to contain か.

In the Classic Engine, the SUBTITUTE function matches the base characters of composite characters. For example, the SUBSTITUTE function considers â to contain a or が to contain か.

Excel equivalent

 SUBSTITUTE

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

Financial performanceFinancial performance in [Region Placeholder] changed by [Revenue Placeholder].
Revenue against targetRevenue in [Region Placeholder] was [Revenue Placeholder]. The target for this period was [Target Revenue Placeholder].

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


TokyoMunichTel AvivAbu Dhabi
Revenue$213,458$648,751$366,951$104,853
Operating costs$153,948$486,795$295,657$54,843

Operating profit

Revenue - Operating costs

$59,510161,956$71,294$50,010

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


TokyoMunichTel AvivAbu Dhabi

Financial performance template with region

SUBSTITUTE(Email Templates.'Financial performance', "[Region Placeholder]", NAME(ITEM(Region)))

Financial performance in Tokyo changed by [Revenue Placeholder].Financial performance in Munich changed by [Revenue Placeholder].Financial performance in Tel Aviv changed by [Revenue Placeholder].Financial performance in Abu Dhabi changed by [Revenue Placeholder].

Financial performance template with region and revenue

SUBSTITUTE(Financial performance template with region, "[Revenue Placeholder]",  TEXT(Profit and loss summary.'Revenue') & " USD")

Financial performance in Tokyo changed by 213458 USD.Financial performance in Munich changed by 648751 USD.Financial performance in Tel Aviv changed by 366951 USD.Financial performance in Abu Dhabi changed by 104853 USD.

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.

TEXT

For example, you can use the TEXT function to convert numeric values to text values for use in other functions that require text-based arguments.

Syntax

TEXT(Number to convert)

Arguments

ArgumentData typeDescription
Number to convertNumberThe value to convert to text.

The TEXT function returns a text result.

Additional information

Results for numbers less than 0.001 display in computerized scientific notations. For example, TEXT(0.0001) = '1.0E-4'.

Calculation engine functionality differences

In Polaris, the TEXT function returns a text value of NaN when used with the numerical value NaN.

In the Classic Engine, the TEXT function returns a blank value when you use it with the numerical value value NaN (Not a Number).

Excel equivalent

TEXT

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.

TEXTLIST

For example, you can produce a list of products sold in a given region.

Syntax

TEXTLIST(Text to concatenate, Separator, List to reference [, Duplicate behavior])

Arguments

ArgumentData typeDescription
Text to concatenate (required)Text line itemThe line item that contains the text values to concatenate.
Separator (required)TextThe text value to use as a separator between each text value concatenated.
List to reference (required)ListThe list that determines the order in which to concatenate the values from the Text to concatenate argument.
Duplicate behaviorKeywordDetermines whether duplicated text values should be listed once or multiple times.

The TEXTLIST function returns a text value.

Duplicate behavior keywords

KeywordDescription
ALL

If the Text to concatenate argument contains multiple instances of the same text, each instance is listed in the result.

The default behavior if you omit the Duplicate behavior argument.

UNIQUEIf the Text to concatenate argument contains multiple instances of the same text, only the first instance is listed in the result.

Syntax example

TEXTLIST('Regional product sales.Products', ", ", Regions, UNIQUE)

Additional information

Use TEXTLIST with the Users list

You can reference the Users list with the TEXTLIST function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

Constraints

Character limit

The TEXTLIST function has a limit of 10,000 characters. This is to prevent the creation of text values that are too large and affect model performance.

If the result of the text function would exceed 10,000 characters, the function stops appending characters and adds an ellipsis (…) to indicate there's more.

Calculation engine functionality differences

You cannot use the TEXTLIST calculation function in Polaris.

In the Classic engine, you can.

Excel equivalent

CONCATENATE

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


CustomerTransaction Value
Transaction 01
0
Transaction 02Fairgreen Furniture Ltd20,000
Transaction 03Carpenter Oak Ltd15,000
Transaction 04Carpenter Oak Ltd20,000
Transaction 05Bluebottle Inc5,000
Transaction 06
0
Transaction 07Purple Haze & Co2,000
Transaction 08Fairgreen Furniture Ltd1,000
Transaction 09Brass Monkeys Inc1,000
Transaction 10Purple Marine Stores1,000
Transaction 11
0
Transaction 12Purple Marine Stores5,000

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


List of all customers

TEXTLIST(Sales Transactions.Customer, ", ", Transactions, ALL)

List of all customers without duplicates

TEXTLIST(Sales Transactions.Customer, ", ", Transactions, UNIQUE)

Total Transactions Value

Sales Transactions.Transaction Value

LondonFairgreen Furniture Ltd, Carpenter Oak Ltd, Carpenter Oak Ltd, Bluebottle Inc, Purple Haze & Co, Fairgreen Furniture Ltd, Brass Monkeys Inc, Purple Marine Stores, Purple Marine StoresFairgreen Furniture Ltd, Carpenter Oak Ltd, Bluebottle Inc, Purple Haze & Co, Brass Monkeys Inc, Purple Marine Stores66,000
BirminghamFairgreen Furniture Ltd, Fairgreen Furniture Ltd, Brass Monkeys Inc, Purple Marine StoresFairgreen Furniture Ltd, Brass Monkeys Inc, Purple Marine Stores110,000
UK

176,000
ParisÉlectricité Berlioz S.A., Amiot S.AÉlectricité Berlioz S.A., Amiot S.A75,000
LyonDufay S.A., Dufay S.A., Jean Françaix S.A, René Désiré S.A.Dufay S.A., Jean Françaix S.A, René Désiré S.A.104,000
France

179,000
MunichHans-Jürgen GmbH, Von Bose AGHans-Jürgen GmbH, Von Bose AG58,000
BerlinKlughardt GmbH, Offenbach AG, Offenbach AG, Telemann GmbHKlughardt GmbH, Offenbach AG, Telemann GmbH202,000
Germany

260,000
New YorkAnderson International Inc., Cummings Industries Inc., Headley Inc., Headley Inc.Anderson International Inc., Cummings Industries Inc., Headley Inc.200,000
Los AngelesKennedy Inc, ABC IncKennedy Inc, ABC Inc20,000
USA

220,000
Total Company

835,000
TRIM

For example, if text imported from an external system includes irregular spaces, you can use the TRIM function to remove the spaces.

Syntax

TRIM(Text)

Arguments

ArgumentData typeDescription
TextTextThe text to trim extra spaces from.

The TRIM function returns a text value.

Syntax example

TRIM(" Account  Summary ")

Calculation engine functionality differences

You cannot currently use the TRIM function in Polaris.

In the Classic Engine, you can.

Excel equivalent

TRIM(opens external page)

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.


AmericaEuropeAustralia
Office locationsLas
Vegas
 London     Melbourne
TRIM(Office locations)Las VegasLondonMelbourne
UPPER

Syntax

UPPER(Text [, Locale])

Arguments

ArgumentData typeDescription
Text (required)TextThe text to make uppercase.
LocaleKeyword

The locale to use. Only has an effect with certain languages.

The available locales are based on ISO 639 and ISO 3166 codes.

The UPPER function returns a text value.

Syntax example

UPPER(Business area)

Additional information

Effect of Locale argument

The Locale argument accepts ISO language codes that correspond to the locales available in Java 8. You can use either:

  • Two letter ISO 639 language codes such as en, fr, de, or tr.
  • Four letter codes made of both ISO 639 language codes and ISO 3166 country codes such as en-us, fr-be, de-at, or tr-tr. However you must replace the hyphen (-) with an underscore (_) in Anaplan, so these become en_us, fr_be, de_at, and tr_tr respectively.

The Locale argument only has an effect on certain languages. For example, Turkish, which is represented by the tr code. In Turkish, there are four versions of the character i. Lowercase and uppercase versions, both with and without a dot.

Calculation engine functionality differences

In Polaris, you cannot use the Locale argument with the UPPER function.

In the Classic Engine, you can use the Locale argument.

Excel equivalent

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.


New YorkSan FranciscoLondonYorkTokyoOsaka
Product categoriesAccessories, Footwear, Innerwear, Outerwear, Sports EquipmentAccessories, Footwear, Sports equipment, Accessories, Footwear, Innerwear, Outerwear, Sports EquipmentOuterwear, Sports EquipmentAccessories, Footwear, Innerwear, Outerwear, Sports EquipmentAccessories, Outerwear, Sports Equipment

Uppercase product categories

UPPER(Product categories)

ACCESSORIES, FOOTWEAR, INNERWEAR, OUTERWEAR, SPORTS EQUIPMENTACCESSORIES, FOOTWEAR, SPORTS EQUIPMENT,ACCESSORIES, FOOTWEAR, INNERWEAR, OUTERWEAR,  SPORTS EQUIPMENTOUTERWEAR, SPORTS EQUIPMENTACCESSORIES, FOOTWEAR, INNERWEAR, OUTERWEAR, SPORTS EQUIPMENTACCESSORIES, OUTERWEAR, SPORTS EQUIPMENT

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.


Accented characterCyrillicGreekChineseJapaneseKorean
Lowercase characteråжψ

Uppercase character

UPPER(Lowercase character)

ÅЖΨ

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 İ.

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.