Formulas Quick Reference
Data Functions
Row64 contains a Data Functions menu to help you get started with analyzing dataframes. The options in this menu allow you to insert code examples of various function commands and formulas. To get started, click on the Data Functions button in the top bar under the Data tab.
This pops up a window that displays all the categories of Data Functions that you can selct from. Click into any one of the categories to see a list of available options.
From the list of functions, you can click on a row to insert the example into your Dataframe editor.
Business
The Business category includes examples of common business and financial analytics.
Accounting
Over Budget
Adds a new column which indicates whether a value is over or under a given budget.
ADDCOLUMN("Over Budget", IF(A1 > 500, "OVER", "UNDER"))Commission
Adds a new column which applies a percentage commission multiplier on a value.
ADDCOLUMN("Commission", B1 * .15)Moving Average
Moving Average - 5 Day
Adds a new column containing the moving average across 5 periods.
ADDCOLUMN("Moving Average", AVERAGE(B1,B2,B3,B4,B5))Moving Average - 10 Day
Adds a new column containing the moving average across 10 periods.
ADDCOLUMN("Moving Average", AVERAGE(B1,B2,B3,B4,B5,B6,B7,B8,B9,B10))Ratio Analysis
Percentage of Max
Adds a new column containing the percentage of the max of a given column (rounded to 4 decimals).
ADDCOLUMN("Perc Of Max", ROUND(B1/MAX(B:B)*100,4))Percentage of Total
Adds a new column containing the percentage of the total sum of a given column (rounded to 4 decimals).
ADDCOLUMN("Perc Of Total", ROUND(B1/SUM(B:B)*100,4))Cleaning
The Cleaning category includes examples of cleaning and processing numeric or text data.
Case
Lower Case
Adds a new column with text values converted to lower case.
ADDCOLUMN("Lower Case", LOWER(B1))Proper Case
Adds a new column with text values converted to proper case (first letter is capitalized).
ADDCOLUMN("Proper Case", PROPER(B1))Upper Case
Adds a new column with text values converted to upper case.
ADDCOLUMN("Upper Case", UPPER(B1))Clean Numbers
Replace Null With Previous
Adds a new column that replaces NULL values with the previous row value.
ADDCOLUMN("Data", IF(ISNULL(A1),A0,A1))Replace Null With Zero
Adds a new column that replaces NULL values with 0.
ADDCOLUMN("Data", IF(ISNULL(A1),0,A1))Clean Strings
Detect Blank Strings
Adds a new column that indicates if a text value is blank.
ADDCOLUMN("Is Blank", ISBLANK(A1))Remove Non-Printable Characters
Adds a new column with text values where non-printable characters are removed.
ADDCOLUMN("Output", CLEAN("text"&CHAR(10)))Replace Null With Blank
Adds a new column that replaces NULL values with blanks.
ADDCOLUMN("Data", IF(ISNULL(A1),"",A1))Trim White Space
Adds a new column with text values where extra spaces are removed.
ADDCOLUMN("Trim", TRIM(A1))Processing
Categorical
Adds a new column containing a 0 or 1 flag that indicates if a categorical value found.
ADDCOLUMN("Categorical", IF(ISERROR(FIND(\"Intel\",B1)),0,1))Column Formula
The Column Formula category includes numeric and text operations that can be applied when adding a new column.
Add Generated Column
Random Integer
Adds a new column containing random integers from 0 to 100.
ADDCOLUMN("Random", INT(RAND()*100))Random Number
Adds a new column containing random numbers from 0 to 1.
ADDCOLUMN("Random", RAND())Logic Formulas
IF
Adds a new column that returns 1 if a given condition is true, otherwise returns 0.
ADDCOLUMN("If", IF(A1>B1,1,0))Number Formulas
ABS
Adds a new column returning the absolute value of the given input.
ADDCOLUMN("ABS Value", ABS(A1*2))CEILING
Adds a new column that contains the nearest integer rounded up.
ADDCOLUMN("Ceiling", CEILING(A1,1))FIXED
Adds a new column that rounds a number to the number of decimal places specified.
ADDCOLUMN("Output", FIXED(A1,2))FLOOR
Adds a new column that contains the nearest integer rounded down.
ADDCOLUMN("Floor", FLOOR(A1,1))INT
Adds a new column that contains the nearest integer rounded down.
ADDCOLUMN("Int", INT(A1))LCM
Adds a new column that returns the least common multiple of the inputs.
ADDCOLUMN("Lcm", LCM(A1,2))MOD
Adds a new column that returns the modulus, or the remainder portion, of a division.
ADDCOLUMN("Mod", MOD(A1,3))MROUND
Adds a new column that rounds a number away from zero to the nearest multiple.
ADDCOLUMN("Output", MROUND(A1,2))ODD
Adds a new column that returns TRUE if a number is odd.
ADDCOLUMN("Output", ODD(A1))POWER
Adds a new column that returns the input number raised to a power.
ADDCOLUMN("Output", POWER(A1,2))PRODUCT
Adds a new column that returns the product of two input numbers.
ADDCOLUMN("Output", PRODUCT(A1,2.1))QUOTIENT
Adds a new column that returns the quotient, or the integer portion, of a division.
ADDCOLUMN("Output", QUOTIENT(A1,5))ROUNDDOWN
Adds a new column that rounds a number towards zero to the given number of digits.
ADDCOLUMN("Output", ROUNDDOWN(A1,0))ROUNDUP
Adds a new column that rounds a number away from zero to the given number of digits.
ADDCOLUMN("Output", ROUNDUP(A1,0))SIGN
Adds a new column that returns the sign of a number.
ADDCOLUMN("Output", SIGN(A1))SQRT
Adds a new column that returns the square root of a number.
ADDCOLUMN("Output", SQRT(A1))SUM
Adds a new column that returns the sum of the inputs.
ADDCOLUMN("Output", SUM(A1, 1.23, 3.4))TRUNC
Adds a new column that truncates a number by removing the fractional part.
ADDCOLUMN("Output", TRUNC(A1))Text Formulas
CHAR
Adds a new column that returns the character specified by an ASCII number (between 1 and 127).
ADDCOLUMN("Output", CHAR(42))CLEAN
Adds a new column that removes all non-printable characters from the input text.
ADDCOLUMN("Output", CLEAN(A1))CODE
Adds a new column that returns the ASCII number of the input character.
ADDCOLUMN("Output", CODE(A1))DOLLAR
Adds a new column that converts the input to dollar currency format.
ADDCOLUMN("Output", DOLLAR(A1*1.6))EXACT
Adds a new column that compares two strings and returns TRUE if an exact match is found. Otherwise, returns FALSE.
ADDCOLUMN("Output", EXACT(A1,"Atari"))FIND
Adds a new column that finds a string within another string (case sensitive). Returns TRUE if found. Otherwise, returns FALSE.
ADDCOLUMN("Output", IF(ISERROR(FIND("GeForce",A1)), "Not Found", "Found"))LEFT
Adds a new column that returns the first 5 characters from the left.
ADDCOLUMN("Output", LEFT(A1,5))LEN
Adds a new column that returns length of the input text.
ADDCOLUMN("Output", LEN(A1))LOWER
Adds a new column that converts the input text to lower case.
ADDCOLUMN("Lower Case", LOWER(B1))MID
Adds a new column that returns a substring within the input text.
ADDCOLUMN("Output", MID(A1,3,5))PROPER
Adds a new column that converts the input text to proper case.
ADDCOLUMN("Proper Case", PROPER(B1))REPLACE
Adds a new column that replaces a substring within the intput text with another string.
ADDCOLUMN("Output", REPLACE(A1,3,1,"###"))REPT
Adds a new column that repeats a text string a given number of times.
ADDCOLUMN("Output", A1 & REPT(" | R64 ",4))RIGHT
Adds a new column that returns the first 5 characters from the right.
ADDCOLUMN("Output", RIGHT(A1,5))SEARCH
Adds a new column that searches for a string within another string (not case sensitive). If found, returns the position where the string is found. Otherwise, returns NULL.
ADDCOLUMN("Output", SEARCH("e", A1), "INT")SUBSTITUTE
Adds a new column that substitutes all instances of a given text with another text value. This example replaces all instances of "a" with "~~".
ADDCOLUMN("Output", SUBSTITUTE(A1,"a","~~"))TRIM
Adds a new column that removes all extra spaces from the input text.
ADDCOLUMN("Output", TRIM(A1))UPPER
Adds a new column that converts the input text to upper case.
ADDCOLUMN("Output", UPPER(B1))Data Type
The Data Type category includes examples for null or error checks, type checks, and type conversion.
Check Empty
Check For Blank
Adds a new column that checks if a text value is blank.
ADDCOLUMN("Output", ISBLANK(A1))Check For Null
Adds a new column that checks if a value is NULL.
ADDCOLUMN("Output", ISNULL(A1))Check Error
Check For Error
Adds a new column that checks if a value is an error.
ADDCOLUMN("Error", ISERROR(1/0))IF Error
Adds a new column that returns a specific value if the input is an error, otherwise returns another value if it is not an error.
ADDCOLUMN("Error", IFERROR(1/0,"GOT ERROR"))Check Type
Blank If Not Text
Adds a new column that converts non-text values to a blanks.
ADDCOLUMN("Output", T("hello"))Check For Number
Adds a new column that checks if a value is a number.
ADDCOLUMN("Output", ISNUMBER(123))Check For Text
Adds a new column that checks if a value is text.
ADDCOLUMN("Output", ISTEXT("hello"))Check For Non-Text
Adds a new column that checks if a value is not text.
ADDCOLUMN("Output", IF(ISNONTEXT(BLANK()), "Is Non-Text", "Is Text"))Force Type
Force Int
Adds a new column forces the column type to be INT.
ADDCOLUMN("Output", "1000", "INT")Force Float
Adds a new column forces the column type to be FLOAT.
ADDCOLUMN("Output", "1000", "FLOAT")Force Big Int
Adds a new column forces the column type to be BIGINT.
ADDCOLUMN("Output", "1000", "BIGINT")Force Big Float
Adds a new column forces the column type to be BIGFLOAT.
ADDCOLUMN("Output", "1000", "BIGFLOAT")Force Date Time
Adds a new column forces the column type to be DATETIME.
ADDCOLUMN("Output", VALUE("7-1-2020"), "DATETIME")Force Text
Adds a new column forces the column type to be TEXT.
ADDCOLUMN("Output", "1000", "TEXT")Type Conversion
Number To Dollar
Adds a new column that converts a number to a dollar formatted string.
ADDCOLUMN("Output", DOLLAR(100.11004))Number To Percentage
Adds a new column that converts a number to a percent-formatted string.
ADDCOLUMN("Output", ROUND(B1 * 100,2) & "%")Text To Number
Adds a new column that converts a numeric text string to a number.
ADDCOLUMN("Output", VALUE(\"100\") + 101)Date & Time
The Date & Time category includes examples of common date and time operations. These include date conversion and formatting, time duration, and extracting date details.
Date Conversion
Convert to Excel Time
Adds a new column that converts a number from Python time (nanoseconds from epoch 1970-01-01T00:00) to Excel time (days elapsed since January 1, 1900).
ADDCOLUMN("Output", EXCELTIME(1577880000000000000), "BIGFLOAT")Convert to Python Time
Adds a new column that converts a number from Excel time (days elapsed since January 1, 1900) to Python time (nanoseconds from epoch 1970-01-01T00:00).
ADDCOLUMN("Output", PYTHONTIME(44392.5), "BIGINT")Date From Year, Month, Day
Adds a new column that gets a DATETIME from year, month, and day inputs.
ADDCOLUMN("Output", DATE(2020,9,1), "DATETIME")Date Numbers To Date
Adds a new column that converts a text string that represents a number to DATETIME.
ADDCOLUMN("Output", VALUE("09/01/2000"), "DATETIME")Date Text To Date
Adds a new column that converts a text string that represents a date to DATETIME.
ADDCOLUMN("Output", VALUE("September 1, 2021"), "DATETIME")Date To Excel Time
Adds a new column that converts a text string that represents a date to Excel time (days elapsed since January 1, 1900).
ADDCOLUMN("Output", VALUE("July 15, 2021"))Excel Time To Date
Adds a new column that converts from Excel time (days elapsed since January 1, 1900) to DATETIME.
ADDCOLUMN("Output", 44392, "DATETIME")Elapsed Time
Elapsed Days
Adds a new column that returns the number of days elapsed since a given datetime.
ADDCOLUMN("Output", ROUND(NOW()-A1,2))Elapsed Time
Adds a new column that returns the amount of time elapsed since a given datetime.
ADDCOLUMN("Output", INT(NOW()-A1) &" days, "& HOUR(NOW()-A1) &" hrs, "& MINUTE(NOW()-A1) & " mins")Time Since 8:00 AM
Adds a new column that returns the amount of time since 8:00 AM.
ADDCOLUMN("Time Since 8", HOUR(A1-VALUE("8:00 AM")) & " hrs, " & MINUTE(A1-VALUE("8:00 AM")) & " mins"")Extract Date Details
Extract Day
Adds a new column that returns the day of the month from a numeric date input. Return values are 1 to 31.
ADDCOLUMN("Day", DAY(A1))Extract Fiscal Quarter
Adds a new column that returns the fiscal quarter from a numeric date input. Return values are 1 to 4.
ADDCOLUMN("Fiscal Quarter", QUARTER(A1,4))Extract Hour
Adds a new column that returns the hour from a numeric date input. Return values are 0 to 23.
ADDCOLUMN("Hour", HOUR(A1))Extract Minute
Adds a new column that returns the minute from a numeric date input. Return values are 0 to 59.
ADDCOLUMN("Minute", MINUTE(A1))Extract Month
Adds a new column that returns the month from a numeric date input. Return values are 1 to 12.
ADDCOLUMN("Month", MONTH(A1))Extract Quarter
Adds a new column that returns the quarter from a numeric date input. Return values are 1 to 4.
ADDCOLUMN("Quarter", QUARTER(A1))Extract Quarter Year
Adds a new column that returns the year and quarter from a numeric date input.
ADDCOLUMN("Quarter Year", YEAR(A1) & " Q" & QUARTER(A1))Extract Week
Adds a new column that returns the weekday from a date.
ADDCOLUMN("Weekday", WEEKDAY(A1))Extract Week Text
Adds a new column that returns the weekday name from a date.
ADDCOLUMN("Weekday", CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"))Extract Year
Adds a new column that returns the year from a numeric date input.
ADDCOLUMN("Year", YEAR(A1))Format Dates
Format Dates
Adds a new column that returns a formatted date string.
ADDCOLUMN("Format Date", YEAR(A1) &"/"& MONTH(A1) &"/"& DAY(A1))Relative Dates
1 Year Ago
Adds a new column that contains the date 1 year ago.
ADDCOLUMN("Output", EDATE(TODAY(),-12), "DATETIME")1 Year From Now
Adds a new column that contains the date 1 year from now.
ADDCOLUMN("Output", EDATE(TODAY(),12), "DATETIME")2 Months Ago
Adds a new column that contains the date 2 months ago.
ADDCOLUMN("Output", EDATE(TODAY(),-2), "DATETIME")Today
Adds a new column that contains the date today.
ADDCOLUMN("Date", TODAY(), "DATETIME")Language
The Language category includes examples of common text analysis, transformation, search and match operations. This also includes regex examples and sentiment analysis examples.
Advanced
Join Tokens
Uses JOINTOKENS, which is a type of Token Formula.
It takes a regex delimiter pattern and splits apart a string.
The split elements are reassembled with a new delimiter string.
ADDCOLUMN("Output", RE_JOIN_TOKENS("\d+","__012--345==678##","|"))Replace Nth Token
Uses RE_REPLACE_TOKEN_NTH, which is a type of Token Formula.
It takes a regex delimiter pattern and splits apart a string. The Nth element is then replaced by a string. The orginal string is then reassembled with the replacement, using the orginal delimiters. The counting for the Nth element is 1 based, 1 will be the first split element.
ADDCOLUMN("Output", RE_REPLACE_TOKEN_NTH("\d+","__002__123__987__", "###",2))Join Splitter Tokens
Uses RE_JOIN_SPLITTERS, which is a type of Token Formula.
It takes a regex delimiter pattern and splits apart a string.
The delimiters are captured and joined together by a new delimiter.
This can be useful for interleaving operations where you capture both split elements and delimiters in different passes.
ADDCOLUMN("Output", RE_JOIN_SPLITTERS("\d+","__012--345==678##","|"))Extract Nth Tokens
Uses RE_EXTRACT_TOKEN_NTH, which is a type of Token Formula.
This uses a cycling regex to capture a pattern over and over and then get the Nth item from this repeating crawl.
This is different than RE_EXTRACT_NTH, which uses a single regex to capture regex match groups, and then return the Nth item from those groups.
RE_EXTRACT_TOKEN_NTH instead uses a token cycling approach where a single regex is applied over and over, collecting the first match of each cycle into a list. Then from this list you grab the Nth item.
This can be a very useful alternative for tricky parsing scenarios.
ADDCOLUMN("Output", RE_EXTRACT_TOKEN_NTH("\d+","Item11/Item21/Item31/", 2))Extract
Extract All Digits & Decimals
Uses RE_EXTRACT_ALL, which is a type of Regex Formula.
It extracts all characters that match a given regex pattern. There can be multiple matches and they will be grouped together in a single string. This example extracts all numbers and decimal places.
ADDCOLUMN("Result", RE_EXTRACT_ALL("[0-9.]","aaa2.33.9bcc4"))Extract All Digits
Uses RE_EXTRACT_ALL, which is a type of Regex Formula.
It extracts all characters that match a given regex pattern. There can be multiple matches and they will be grouped together in a single string. This example extracts all digits.
ADDCOLUMN("Result", RE_EXTRACT_ALL("\d","aaa2.33.9bcc4"))Extract Text Containing Word
Uses RE_EXTRACT, which is a type of Regex Formula.
It extracts the first set of characters that match a given regex pattern. In this example, it searches for a word. To search for a different word, replace
".*word.*" with ".*different_word.*".
The ".*" at the beginning and end return the entire text that contains the word.
To get from the word to the end you could search "word.*" instead.
ADDCOLUMN("Result", RE_EXTRACT(".*word.*","testword.jpg"))Contains Word From List
Uses RE_EXTRACT, which is a type of Regex Formula.
This is an example that shows how to select text that contains any word from a list of words.
In this example, we search for a list: word1, word2 ,word3.
Simply replace those with the words you want to search for, and seperate them with the | character
ADDCOLUMN("Result", RE_EXTRACT(".*(word1|word2|word3).*","text and word2"))Starts With Word
Uses RE_EXTRACT, which is a type of Regex Formula.
This example shows how to select text that starts with any word. The ^ symbol is a special character for the start of text.
Just replace word with the word you want to start with.
ADDCOLUMN("Result", RE_EXTRACT("^word.*","word and word2"))US Phone Number
Uses RE_EXTRACT, which is a type of Regex Formula.
This example returns a US phone number if it include a 3-digit area code, a 3-digit exchange code, and a 4-digit subscriber code.
ADDCOLUMN("Result", RE_EXTRACT("^[0-9]{3}-[0-9]{3}-[0-9]{4}$","555-555-5555"))Extract If No Word
Uses RE_EXTRACT, which is a type of Regex Formula.
This example extracts all the input text if a word is not found in it.
To change the word, just replace the text word with your excluded word.
ADDCOLUMN("Result", RE_EXTRACT("^(?!.*word).*","testname.jpg"))Extract Nth Item
Uses RE_EXTRACT_NTH, which is a type of Regex Formula.
This example extracts the Nth item in a regex capture groups. You can learn more about regex capture groups here.
In this example, we capture the 3rd item.
An alternative method to capture the Nth item is RE_EXTRACT_TOKEN_NTH, which uses a token gathering approach and may work better depending on your text extraction scenario.
This method requires more knowlege of Regex capture groups to modify with fine control.
ADDCOLUMN("Result", RE_EXTRACT_NTH("(.*)/(.*)/(.*)/","Item1/Item2/Item3/", 3))Fuzzy Regex Search
Fuzzy Regex Replace
Uses RE_FUZZY_REPLACE, which is a type of Fuzzy Regex Search.
This example uses fuzzy regex search techniques to approximatly search a regex pattern and replace it with another string.
To replace the string you want to search for, replace "abc" with your string. To use a different replacement, change out the string "####".
ADDCOLUMN("Result", RE_FUZZY_REPLACE("abc","000a0bc000", "####"))Fuzzy Regex Match
Uses RE_FUZZY_MATCH, which is a type of Fuzzy Regex Search.
This example uses fuzzy regex search techniques and returns TRUE if it finds a match, and FALSE if it doesn't.
ADDCOLUMN("Result", RE_FUZZY_MATCH("abc","000a0bc000"))Fuzzy Regex Extract
Uses RE_FUZZY_EXTRACT, which is a type of Fuzzy Regex Search.
This example uses fuzzy regex search techniques and searches for a approximate match in a string. When it finds a match, it returns the string it approximatly matched to.
RE_FUZZY_EXTRACT("abc","000a0bc000"))Fuzzy Search
Quick Fuzzy Match
Uses FUZZY_QMATCH, which is a type of Fuzzy Search.
This quick version of fuzzy matching is better suited for bigger data sets. It returns a similarity score between 0 to 100 of 2 strings based on a fuzzy match.
ADDCOLUMN("Result", FUZZY_QMATCH("this is a test!","this is a test"))Fuzzy Replace
Uses FUZZY_REPLACE, which is a type of Fuzzy Search.
It will search for a sub-string in another string. You set a score threshold between 0-100. If a fuzzy match it greater than the threashold score it is substituted with a replacement string.
In this example, set the search string by replacing the text "tast".
Set the threshold by replacing the number 50.
Set the replacement string by swapping the text success.
ADDCOLUMN("Result", FUZZY_REPLACE( "tast", 50, "this is a test of fuzzy", "success"))Fuzzy Match
Uses FUZZY_MATCH, which is a type of Fuzzy Search.
It returns a similarity score between 0 to 100 of 2 strings, based on a fuzzy match. This is the detailed version of the formula and is more suitable for smaller datasets. For larger datasets, use FUZZY_QMATCH.
ADDCOLUMN("Result", FUZZY_MATCH("this is a test!","this is a test"))Fuzzy Contains
Uses FUZZY_CONTAINS, which is a type of Fuzzy Search.
This example returns a similarity score between 0 to 100, based on a fuzzy match. Unlike match, it bases the score on the match of a sub-string, not the comparision of 2 full strings.
ADDCOLUMN("Result", FUZZY_CONTAINS("abc","000a0bc000"))Match
Contains Word
Uses RE_MATCH, which is a type of Regex Formula.
Returns TRUE if a search term is found within text. Returns FALSE if there's no match.
To change the search term, replace the text word in the first argument defining the regex.
ADDCOLUMN("Result", RE_MATCH(".*word","testword.jpg"))Contains File Extensions
Uses RE_MATCH, which is a type of Regex Formula.
Returns TRUE if any of the file extensions listed in a string is found. If none are found, returns FALSE.
The file extension list delimited with the | character. Replace the string gif|png|jpg|jpeg with your own list to search for.
ADDCOLUMN("Result", RE_MATCH("^\w+\.(gif|png|jpg|jpeg)$","filename.jpg"))Doesn't Contain Word
Uses RE_MATCH, which is a type of Regex Formula.
Returns TRUE if a search word is not found. Returns FALSE if it is found.
To change the search word, replace the text word in the first argument.
ADDCOLUMN("Result", RE_MATCH("^(?!.*word).*","testword.jpg"))Valid Email
Uses ISEMAIL, which is a formula designed to reduce the complexity of detecting emails by regex.
Returns TRUE if input text is a valid email, returns false if it's not.
ADDCOLUMN("Result", ISEMAIL(B1))Valid IP Address
Uses RE_MATCH, which is a type of Regex Formula.
Returns TRUE if input text is an IP Address, returns false if it's not.
ADDCOLUMN("Result", RE_MATCH("^((25[0-5]|(2[0-4]|1\d|[1-9]|)\d)\.?\b){4}$","127.0.0.1"))Contains Only Numbers
Uses RE_MATCH, which is a type of Regex Formula.
Returns TRUE if the input text contains only numberic characters. Otherwise, returns FALSE.
ADDCOLUMN("Result", RE_MATCH("^\d+$","24564"))POSIX Regex
POSIX Replace All
Uses REP_REPLACE_ALL, which is a type of POSIX Regex.
This example searches all occurrences of grouped digits in a string, and substitutes them with a replacement string.
To change the search, modify the POSIX search pattern: [[:digit:]].
To change the replacement string, modify "*".
ADDCOLUMN("Result", REP_REPLACE_ALL("[[:digit:]]","15ab453c4d", "*"))POSIX Replace
Uses REP_REPLACE, which is a type of POSIX Regex.
This example searches for the first occurrence of grouped digits in a string, and substitutes them with a replacement string.
To change the search, modify the POSIX search pattern: [[:digit:]].
To change the replacement string, modify "hamster".
ADDCOLUMN("Result", REP_REPLACE("([[:digit:]]+)","dog,cat,23242,rabbit", "hamster"))POSIX Match
Uses REP_MATCH, which is a type of POSIX Regex.
Returns TRUE if input text matches a POSIX regex pattern, returns FALSE if it's not.
To change the POSIX regex pattern, replace the string: ".*t".
ADDCOLUMN("Result", REP_MATCH(".*t","testword"))POSIX Extract
Uses REP_EXTRACT, which is a type of POSIX Regex.
This example searches for the first occurrence of grouped alphabetic characters in a string and extracts them.
To change the POSIX regex pattern, replace the string: "([[:alpha:]]*)".
ADDCOLUMN("Result", REP_EXTRACT("([[:alpha:]]*)","123..bob4@gmail.com"))Replace
Replace First Match
Uses RE_REPLACE which is a type of Regex Formula.
It replaces the first match of a regex pattern with a substitution string.
To change the regex pattern, replace the first argument: "company1".
To change the substitution string, replace the third argument: "company2".
ADDCOLUMN("Result", RE_REPLACE("company1","hello from company1", "company2"))Replace Nth Match
Uses RE_REPLACE_NTH, which is a type of Regex Formula.
It replaces the Nth group match of a regex pattern with a substitution string. You can learn more about regex capture groups here.
To change the regex pattern, replace the first argument: "item1".
To change the substitution string, replace the third argument: "###".
To change the capture group number replaced, replace the fourth argument: 2.
ADDCOLUMN("Result", RE_REPLACE_NTH("item1","item1 item1 item1 item1", "###",2))Replace All Matches
Uses RE_REPLACE_NTH, which is a type of Regex Formula.
It replaces all group match of a regex pattern with a substitution string. You can learn more about regex capture groups here.
To change the regex pattern, replace the first argument: "item1".
To change the substitution string, replace the third argument: "-->".
ADDCOLUMN("Result", RE_REPLACE_NTH("item1","item1 item1 item1 item1", "###",2))Sentiment
Sentiment Value
Uses SENTIMENT, which is a type of Sentiment Formula.
Takes an input string and returns a polarity (positive/negative) score. The score range is from -1 to 1, with -1 being very negative, and 1 being very positive.
ADDCOLUMN("Result", SENTIMENT("That's an awesome idea!"))Transform
The Transform category includes operations to aggregate, filter, group, and pivot data. For detailed explanations of function usage and syntax, please refer to the Functions Reference page.
Data Transformation
Dedup
Sort and remove duplicates from one or more columns.
DEDUP(A:A)Pivot
Pivots a table in two dimensions and fills the values based on the specified operation. In other words, this is a two-dimensional group-by.
In this example, the result table first dimension (going down the rows) is given by unique values in Column A. The second dimension (going across the columns) will be given by unique values in Column B. Within the result table, cells are the sum of Column C values given the unique Columns A and B grouping.
PIVOT(A:A, B:B, C:C, "SUM")Sort
Sort one or more columns.
SORT(A:A)Sort Descending
Sort one or more columns in descending order.
SORTDESCENDING(A:A)Filter & Get Subset
Drop
Drop records where the given condition matches the column. These are basic conditions: =, !=, <, <=, >, >=.
In this example, we drop all records where the value in Column A is equivalent to "Sales Rep".
DEDUP(A:A,"=Sales Rep")DropIf
Drop records that satisfy the formula logic. If the formula evaluation results in a non-zero value, then it is evaluated as TRUE.
DROPIF(A1<50)Filter
Keep records where the given condition matches in the column. These are basic conditions: =, !=, <, <=, >, >=. In this example, we keep all records where the value in Column A is equivalent to "Sales Rep".
FILTER(A:A,"=Sales Rep")FilterIf
Keep records that satisfy the formula logic. If the formula evaluation results in a non-zero value, then it is evaluated as TRUE.
FILTERIF(A1<50)Formulas
Add Column
Adds a new column and applies the given formula. A1 notation within the formula is auto-increment as each row of the is evaluated Dataframe.
ADDCOLUMN("New Column Name", EVEN(B1))Group & Collapse Rows
Group & Average
Group by the specified column(s). Aggregate all other numeric columns by taking the average of each column per group.
GROUPAVERAGE(B:B)Group & Count
Group by the specified column(s). Aggregate all other numeric columns by taking the count of each column per group.
GROUPCOUNT(B:B)Group & Sum
Group by the specified column(s). Aggregate all other numeric columns by taking the sum of each column per group.
GROUPSUM(B:B)Add Operator
Add a custom group-by operator instruction. This allows you to specify a column and the operation to perform on it when the data is grouped. Currently allowed ops include:
- AVERAGE
- COUNT
- SUM
ADDOP(C:C,"SUM")Group By Ops
Group by the specified column(s) and apply all the user-specified ops previously indicated by ADDOP.
GROUPOPS(B:B)Link
CrossLink
Link source data. The source data can be a table from either a Preview or a Dataframe tab.
- CROSSLINK should be the first function command executed, since it defines the data source.
- CROSSLINK should also be called only once.
CROSSLINK("SourceDF", A:A, B:B)