Akeneo supports formulas typically found in most desktop spreadsheet packages. Functions can be used to create formulas that manipulate data and calculate strings and numbers.

Here's a list of all the functions available. 

Information & limits

• Akeneo functions are only available in English.
• You can use up to 10 functions and 5 levels of nesting for one target. 

Multi-locale targets

When a target handles localized values, Akeneo PIM applies transformation per locale. 

Example

In your Akeneo PIM, you have: 

• a SKU identifier attribute with no value per channel and no value per locale,
• a color simple select attribute with no value per channel and no value per locale,
• and a name attribute with no value per channel and value per locale. 

For your e-commerce product name target, you configure a transformation to concatenate these 3 attributes to fill your website product description in several languages. 

Akeneo PIM sends the concatenation of your product SKU, the color label in English, and the name in English for the English locale and does the same for each managed locale. 

List of available functions

CONCATENATE

Appends strings to one another.

Sample usage

CONCATENATE("Welcome", " ", "to", " ", "Akeneo!")
>> Result: “Welcome to Akeneo!”

Syntax

CONCATENATE(string1, [string2, ...])

• string1 - The initial string.
• string2 ... - [ OPTIONAL ] - Additional strings to append in sequence.

Notes

• You can concatenate up to 10 strings. 

MERGE

Transform a list of values into a single string separated by commas.

Sample usage

MERGE(["red", "blue", "yellow"])
>> Result: “red, blue, yellow”

MERGE(categories)
>> Result: “LED TVs, Computer monitors, Samsung”

Syntax

MERGE(array)

• array - The initial array.

FIRST

Return the first item in a table.

Sample usage

FIRST(["red", "blue", "yellow"])
>> Result: “red”

FIRST(categories)
>> Result: “LED TVs”

Syntax

FIRST(array)

• array - The initial array of strings.

SPLIT

Divides text around a specified character or string and puts each fragment into a separate string in a table of strings.

Sample usage

SPLIT("1,2,3", ",")
>> Result is a table of strings: ["1", "2", "3"]

SPLIT("Akeneo Product Cloud", " ")
>> Result is a table of strings: ["Akeneo", "Product", "Cloud"]

SPLIT("Lorem-ipsum dolor et", "-")
>> Result is a table of strings: ["Lorem", "ipsum dolor et"]

SPLIT("printer scanner 3in1 3kg 5306114", "31", TRUE, TRUE) or SPLIT("printer scanner 3in1 3kg 5306114", "31")
>> Result is a table of strings: ["printer scanner", "in", " ", "kg 5", "06", "4"]

SPLIT("printer scanner 3in1 3kg 5306114", "31", TRUE, FALSE)
>> Result is a table of strings: ["printer scanner", "in", " ", "kg 5", "06", "", "4"]

SPLIT("printer scanner 3in1 3kg 5306114", "31", FALSE, FALSE)
>> Result is a table of one string: ["printer scanner 3in1 3kg 5306114"]

Syntax

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

• text - The text to divide.
• delimiter - The character or characters to use to split text.
  • By default, each character in delimiter is considered individually, e.g. if delimiter is "31", then text is divided around the characters "3" and "1". Set split_by_each to FALSE to turn off this behavior.
• split_by_each - [ OPTIONAL - TRUE by default ] - Whether or not to divide text around each character contained in delimiter.
• remove_empty_text - [ OPTIONAL - TRUE by default ] - Whether or not to remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty source values are added between consecutive delimiters.

Notes

• Note that the character or characters to split the string around will not be contained in the result themselves.

UPPER

Convert a specified string to uppercase.

Sample usage

UPPER("lorem ipsum") 
>> Result: “LOREM IPSUM”

Syntax

UPPER(text)

• text - The string to convert to uppercase.

LOWER

Convert a specified string to lowercase.

Sample usage

LOWER("LOREM IPSUM") 
>> Result: "lorem ipsum"

Syntax

LOWER(text)

• text - The string to convert to lowercase.

PROPER

Capitalize each word in a specified string.

Sample usage

PROPER("united states") 
>> Result: United States

PROPER("uNITed statES") 
>> Result: United States

PROPER("unitedstates") 
>> Result: Unitedstates

Syntax

PROPER(text)

• text - The text which will be returned with the first letter of each word in uppercase and all other letters in lowercase.

Notes

• PROPER is useful for proper nouns, such as names of people or geographic locations.
• PROPER capitalizes each word in text rather than the beginning of each sentence, and is, therefore, likely not the correct tool to use for paragraphs or other blocks of text.
• PROPER will convert all characters not at the beginning of words to lowercase, which may cause problems with certain strings. For example, using PROPER("mcLeod") to capitalize the surname McLeod results in "Mcleod" instead.

LEFT

Return a substring from the beginning of a specified string.

Sample Usage

LEFT("Akeneo", 2)
>> Result: Ak

LEFT("lorem ipsum")
>> Result: l

Syntax

LEFT(string, [number_of_characters])

• string - The string from which the left portion will be returned.
• number_of_characters - [ OPTIONAL - 1 by default ] - The number of characters to return from the left side of string.

Notes

• 0 is a valid input for number_of_characters and will cause LEFT to return the empty string.

TRIM

Remove leading, trailing, and repeated spaces in text.

Sample Usage

TRIM(" Akeneo")
>> Result: "Akeneo"

TRIM("lorem ipsum ")
>> Result: "lorem ipsum"

Syntax

TRIM(text)

• text - The string or reference to a source containing a string to be trimmed.

Notes

• TRIM removes all spaces in a text string, leaving just a single space between words.
• Whitespace or non-breaking space will not be trimmed.

REGEXREPLACE

Replace part of a text string with a different text string using regular expressions.

Sample usage

REGEXREPLACE("Akeneo 101", "[0-9]+","777") 
>> Result: "Akeneo 777"

REGEXREPLACE("The price is $826.25","[0-9]*\.[0-9]+[0-9]+", "315.75") 
>> Result: “The price is $315.75”

REGEXREPLACE("(Content) between brackets", "\(([A-Za-z]+)\)", "Word") 
>> Result: “Word between brackets"

Syntax

REGEXREPLACE(text, regular_expression, replacement)

• text - The text, a part of which will be replaced.
• regular_expression - The regular expression. All matching instances in text will be replaced.
• replacement - The text which will be inserted into the original text.

Notes

• We support PCRE2 (PHP >= 7.3). Learn more on how to use PCRE2 expressions. You can also use the regex101 website to test your regular expression. 
• This function only works with text (not numbers) as input and returns text as output.

REGEXEXTRACT

Extract the first matching substrings according to a regular expression.

Sample usage

REGEXEXTRACT("My favorite number is 241, but my friend's is 17", "\d+")
>> Result: "241"

Tip: REGEXEXTRACT will return "241" in this example because it returns the first matching case.

Syntax

REGEXEXTRACT(text, regular_expression)

• text - The input text.
• regular_expression - The first part of text that matches this expression will be returned.

Notes

• We support PCRE2 (PHP >= 7.3). Learn more on how to use PCRE2 expressions. You can also use the regex101 website to test your regular expression. 
• This function only works with text (not numbers) as input and returns text as output.

CLEANHTML

Clean all the HTML tags automatically in your textarea attributes used as a source.

Sample usage

CLEANHTML("My product description with <span style="font-weight: bold;">bold</span> and <span style="font-style: italic;">italic</span> text.")
>> Result: "My product description with bold and italic text."

Syntax

CLEANHTML(text)

• text - The input text to clean.

ISEMPTY

Checks whether the attribute, property or value is empty.

Sample usage

ISEMPTY("value")
>> Result: FALSE

ISEMPTY("")
>> Result: TRUE

ISEMPTY(null)
>> Result: TRUE

Syntax

ISEMPTY(value)

• value - Reference to the attribute or property that will be checked for emptiness. valuecan be a string or a table of strings.
  • When value is a string, ISEMPTY returns TRUE if value is empty or null, and FALSE if it contains characters.
  • When value is a table of strings, ISEMPTY returns TRUE if the table is empty or null, and FALSE if it contains at least a string with characters.

Notes

• This function is most often used in conjunction with IF in conditional statements.

IFS

Evaluate multiple conditions and return a value corresponding to the first true condition.

Sample usage

IFS(title = "", "Default title", title = null, "Default title", title <> "" AND title <> null, title)
>> Result if the source title is empty or null: "Default title"
>> Result if the source title has a value: "The title value"

Syntax

IFS(condition1, value1, [condition2, value2, …])

• condition1 - The first condition to be evaluated. 
• value1 - The returned value if condition1 is TRUE.
• condition2, value2, … - Additional conditions and values if the first one is evaluated to be false.

Notes

• You can add up to 5 condition/value combinations. 
• If all conditions are FALSE, an error is returned. 

ROUND

Capitalize each word in a specified string.

Sample usage

ROUND(826.645, 0) 
>> Result: 827

ROUND(826.645) 
>> Result: 827

ROUND(826.645, 1) 
>> Result: 826.6 (decimal number place 1)

ROUND(826.645, 2) 
>> Result: 826.65 (decimal number place 2)

ROUND(826.645, -1) 
>> Result: 830 (decimal number place -1)

ROUND(826.645, -2) 
>> Result: 800  (decimal number place -2)

Syntax

ROUND(value, [places])

• value - The value to round to places number of places.
• places - [ OPTIONAL - 0 by default ] - The number of decimal places to which to round.

Notes

• Places may be negative, in which case value is rounded at the specified number of digits to the left of the decimal point.
• Standard rules indicate that the next most significant digit (the digit to the right) is considered when rounding to a particular place. It is rounded up if this digit is greater than or equal to 5. Otherwise, it is rounded down.