Functions produce an output called a return value (which could be a number, text, true/false value, or date type, depending on the function). Most functions require parameters
. Parameters are the inputs into the function, and are specified in parentheses after the function name and separated by commas. The order of parameters and data format of each parameter is important. When you insert a function from a menu, placeholder text for each parameter will be inserted. You must replace the placeholder text for each parameter to complete the function. Functions with “...”
can accept any number of additional parameters.
For instructions on how to use functions in custom formula columns, read Create a custom formula column.
| Category | Return Type | Function | Description |
| Arithmetic | Number | round(x)
|
Rounds x
to the nearest integer. Learn more
|
| Arithmetic | Number | floor(x)
|
Returns the greatest integer less than or equal to |
| Arithmetic | Number | ceiling(x)
|
Returns the smallest integer greater than or equal to |
| Logic | Varies | if(condition, if_true, if_false)
|
Evaluates condition
and returns if_true
if the condition is true and if_false
if the condition is false. The return value will be the same type as if_true
and if_false
. Learn more
|
| Logic | True/False | or(x, y, ...)
|
Returns True
if one of the parameters is true. Learn more
|
| Logic | True/False | not(x)
|
Returns True
if x
is false and returns False
if x
is true. Learn more
|
| Logic | True/False | and(x, y, ...)
|
Returns True
if all parameters are true. Learn more
|
| Logic | Boolean | is_not_finite(value)
|
Determines if a number is finite and returns true or false. Learn more |
| Logic | Boolean, number, or text | if_error(value, error_value)
|
Evaluates "value". If "value" returns an error, the function evaluates "alternate". Learn more |
| Logic | Boolean, number, or text | is_error(value)
|
Evaluates to true if "value" is an error. Learn more |
| Logic | Boolean or number | if_not_finite(value, alternate)
|
Evaluates "value". If "value" is not a finite number, the functions evaluates "alternate". Learn more |
| Text | Text | concat(text1, text2, ...)
|
Combines 2 or more text strings. Learn more |
| Text | Text | Substitute_all
|
Replaces multiple values in a string with a single value. Learn more |
| Text | Text | replace("string", start, length, "new_text")
|
Replaces characters in the string with the new text, starting at position "start" and counting "length" characters. Learn more |
| Text | Text | mid("text", start, length)
|
Returns a substring of the specified string starting at the specified character and including the specified number of characters. Learn more |
| Text | Text | clean_keyword("text", "replacement")
|
Scans the string and replaces characters that Search Ads 360 does not support for keyword text with the replacement string. Learn more |
| Text | Number | string_len_byte("value")
|
Returns the number of bytes in a string. Learn more |
| Text | Number | search_byte("needle", "haystack")
|
Returns the starting position in bytes of the first instance of "needle" in "haystack". Learn more |
| Text | Text | regex_extract("value", "regular_expression")
|
Searches a string and returns text that matches a regular_expression. Learn more |
| Text | Boolean | regex_match("value", "regular_expression")
|
Evaluates as true if the string matches all or part of a regular expression. Learn more |
| Text | Text | regex_replace("value", "regular_expression", "replacement")
|
Searches a string for text that matches a regular_expression, then replaces the matching text with new_text. Learn more |
| Text | Text | bmm("value")
|
Adds the "+" character to the beginning of each word of the string. Learn more |
| Text | Text | reduce_word("value", length, "delimiter")
|
Returns as many characters as possible, starting from the beginning of a string and counting up to a delimiter, while staying within the specified maximum length. Learn more |
| Text | Text | title_case("value")
|
Converts a string to title case. Learn more |
| Text | Text | unescape("value")
|
Decodes a URL that has been URL encoded. Learn more |
| Text | Text | &
|
Concatenates two Strings (only applicable for templates). Learn more |
| Text | Text | substitute_case_insensitive("haystack", "needle", "replacement")
|
Substitutes all instances (case insensitive) of "needle" in "haystack" with the replacement text. No option for "occurrence". Learn more |
| Text | Text | escape("value")
|
Encodes an URL. Learn more |
| Text | True/False | is_empty(text)
|
Returns True if text
has no characters. Learn more
|
| Text | Text | trim(text)
|
Removes whitespace around a text string. Learn more |
| Text | True/False | starts_with(needle, haystack)
|
Returns |
| Text | True/False | ends_with(needle, haystack)
|
Returns True
if haystack
text string ends with needle
. Learn more
|
| Text | Number | search(needle, haystack)
|
Returns the starting position of the first instance of needle
in haystack
. Learn more
|
| Text | True/False | contains(needle, haystack)
|
Returns True when haystack
text string contains needle
. Learn more
|
| Text | Text | right(text, length)
|
Returns the last length
characters of text
. Learn more
|
| Text | Text | left(text, length)
|
Returns the first length
characters of text
. Learn more
|
| Text | Text | substitute(haystack, needle, replacement)
|
Substitutes all instances of needle
in haystack
with replacement
. Learn more
|
| Text | Text | upper(text)
|
Converts all characters in text
to UPPERCASE. Learn more
|
| Text | Text | lower(text)
|
Converts all characters in text
to lowercase. Learn more
|
| Text | Number | length(text)
|
Return the number of characters in text. Learn more |
| Conversion | Text | to_text(value)
|
Converts value
to text. Learn more
|
| Conversion | Text | to_text_with_precision(value, precision)
|
Converts any numeric data type to a string with a precise decimal points. Learn more |
| Conversion | Number | extract_percent("XX%")
|
Reads percentage and returns the value as a decimal. Learn more |
| Conversion | Number | to_number(value)
|
Converts value
to a number. Learn more
|
| Date | Date | date(year, month, day)
|
Returns a date with the specified year, month, and day. Learn more |
| Date | Number | day(date)
|
Returns the day of the month from date
. Learn more
|
| Date | Number | month(date)
|
Returns the month of the year (1 through 12) from date
. Learn more
|
| Date | Number | quarter(date)
|
Returns the quarter of the year (1 through 4) from date
. Learn more
|
| Date | Number | year(date)
|
Returns the year of date
as a number. Learn more
|
| Date | Number | weekday(date)
|
Returns the day of the week (1 through 7) for date
where Sunday is 1. Learn more
|
| Date | Number | weeknum(date(year, month, day))
|
Returns the current week number in a year (1 through 54) for date
where weeks start on Sunday. Learn more
|
| Date | Number | daynum(date(year, month, day))
|
Returns the day of the year (1 through 355 or 356) for date
. Learn more
|
| Date | Number | days_between(date1, date2)
|
Returns the number of days between date1
and date2
. Learn more
|
| Date | Date | offset_days(date(year, month, day), days)
|
Add [ days
] days to date
, counting forward/backward days on a calendar. Learn more
|
| Date | Date | offset_months(date(year, month, day), months)
|
Add months
to the month component of date
; if the resulting month doesn't have enough days for the day component, the last day of the new month is returned. Learn more
|
| Date | Date | offset_years(date(year, month, day), years)
|
Adds years
to the year component of date. Learn more
|
| Date | Date | today()
|
Returns the current date in custom column owner's account timezone. Learn more |
| Date | Date | yesterday()
|
Returns the previous day's date in custom column owner's account timezone. Learn more |
| Date | Date | week_start_sunday()
|
Returns the date of the most recent Sunday in custom column owner's account timezone. Learn more |
| Date | Date | week_start_monday()
|
Returns the date of the most recent Monday in custom column owner's account timezone. Learn more |
| Date | Date | month_start()
|
Returns the first date in the current month in custom column owner's account timezone. Learn more |
| Date | Date | year_start()
|
Returns the first date in the current year (January 1) in custom column owner's account timezone. Learn more |
| Date | Date | report_range_start()
|
Returns the date at the beginning of the report’s current time range. Learn more |
| Date | Date | report_range_end()
|
Returns the date at the end of the report’s current time range. Learn more |
| Date | Number | report_days_count()
|
Returns the number of days covered by the report based on its start and end date. Learn more |
| Aggregation | Varies | any_with_max(value, order_value)
|
Returns a value from an item that has the highest value for a specified attribute. Learn more |
| Aggregation | Varies | any_with_min(value, order_value)
|
Returns a value from an item that has the lowest value for a specified attribute. Learn more |
| Aggregation | Varies | max_with_max(value, order_value)
|
Returns the highest value from a group of items that has the highest value for a specified attribute. Learn more |
| Aggregation | Varies | max_with_min(value, order_value)
|
Returns the highest value from a group of items that has the lowest value for a specified attribute. Learn more |
| Aggregation | Varies | min_with_max(value, order_value)
|
Returns the lowest value from a group of items that has the highest value for a specified attribute. Learn more |
| Aggregation | Varies | min_with_min(value, order_value)
|
Returns the lowest value from a group of items that has the lowest value for a specified attribute. Learn more |
| Aggregation | Varies | any(value)
|
Chooses a value for an inventory attribute from one of the items selected by a template. Learn more |
| Aggregation | Varies | max(value)
|
Returns the highest value of an attribute among a group of items. Learn more |
| Aggregation | Varies | min(value)
|
Returns the lowest value of an attribute among a group of items. Learn more |
| Aggregation | Number | count()
|
Returns the number of unique inventory items selected by an inventory template's multipliers and filters. Learn more |
| Aggregation | Number | count_with_max(value, order_value)
|
Returns the number of items with the highest value for a specified attribute. Learn more |
| Aggregation | Number | count_with_min(value, order_value)
|
Returns the number of items with the lowest value for a specified attribute. Learn more |
| Other | TRUE | True
|
Constant true value. Learn more |
| Other | FALSE | False
|
Constant false value. Learn more |
| Other | Boolean | =, <>, <, >, <=, >=
|
Compares two data items and evaluates to true or false. Learn more |
