Functions in custom formula columns

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 x. Learn more

Arithmetic Number ceiling(x)

Returns the smallest integer greater than or equal to x. Learn more

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 True if haystack text string begins with needle. Learn more

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

Was this helpful?

How can we improve it?

Need more help?

Try these next steps:

Search
Clear search
Close search
Google apps
Main menu
18194212012495582519
true
Search Help Center
true
true
true
true
true
5055977
false
false