Text Formulas
Concat
Formula
=concat(<input1>, [input2], ...)
Description
Concatenates all given inputs from left to right.
Arguments
Argument
Required (Y/N)
Description
Input1
Y
The input to which following inputs will be appended
Input2
N
The second and any additional inputs to append to the first input
Examples
Example 1:
=concat($"FirstName", "-", $"LastName")="Albert-Einstein"Example 2:
=concat("a","b","C","D")="abCD"
Find
Formula
=find(<input>, <searchTerm>, [startPosition])
Description
Returns the zero-based index of the given search term or null if it is not found.
Arguments
input
Y
The string to search within
searchTerm
Y
The search term to search for
startPosition
N
The one-based index to start searching from. Optional, defaults to 1.
Examples
Example 1:
=find("tacocat", "taco")=>0Example 2:
=find("tacocat", "cat")=>4Example 3:
=find("tacocat", "dog")=>nullExample 4:
=find("tacocat", "taco", 2)=>nullExample 5:
=find("ab ab ab", "ab", 3)=>4
rFind
Formula
=rfind(<input>, <searchTerm>, [startPosition])
Description
Returns the one-based index of the last occurrence of the given search term or null if it is not found.
Arguments
input
Y
The string to search within
searchTerm
Y
The search term to search for
startPosition
N
The one-based index to start searching from. Optional, defaults to 1.
Examples
Example 1:
=rfind("ab ab ab", "ab")=>7Example 2:
=rfind("abc xyz abc", "abc", 5)=>9Example 3:
=rfind("abc xyz", "abc", 5)=> (null)
Len
Formula
=len(<input>)
Description
Returns the length of a given input.
Arguments
input
Y
Input to find the length of
Examples
Example 1:
=len("tacocat")=>7Example 2:
=len("")=>0
Replace (Substitute)
Formula
=replace(<input>, <searchTerm>, <replacement>, [instances])
=substitute(<input>, <searchTerm>, <replacement>, [instances])
Description
Replaces any found occurrences of the search term with the replacement string. If instances are specified, only those instances will be replaced. If instances are not specified, all instances will be replaced.
Arguments
input
Y
The string to search and replace within
searchTerm
Y
Substring to search for
replacement
Y
Substring to replace occurrences of the search term with
instances
N
An optional list of instances to replace, starting at 1
Examples
Example 1:
=replace("tacocat", "taco", "burrito")=>"burritocat"Example 2:
=replace("tacocat", "taco", "")=>"cat"Example 3:
=replace("tacocat", "cat", "dog")=>"tacodog"Example 4:
=replace("tacocat", "dog", "horse")=>"tacocat"Example 5:
=replace("aaaaaa", "a", "b", 1, 2, 4)=>"bbabaa"
Substring (Mid)
Formula
=substring(<input>, <start>, <count>)
=mid(<input>, <start>, <count>)
Description
Returns a substring of the given input starting at the given start index and continuing for the given count of characters.
Arguments
input
Y
The string to search within
start
Y
The index at which to begin the substring, starting at 1
count
Y
The number of characters to take
Examples
Example 1:
=substring("tacocat", 1, 4)=>"taco"
=mid("tacocat", 1, 4)=>"taco"Example 2:
=substring("tacocat", 5, 3)=>"cat"
Skip
Formula
=skip()
Description
Skips the given row.
Arguments
Argument
Required (Y/N)
Description
N/A
N/A
N/A
Examples
Example 1:
=if(equals($"Cost", 0), skip(), $"Cost")= [skips row OR lists cost]
Skipping a row means the row will not be included in the output.
ToCamelCase
Formula
=tocamelcase(<input>)
Description
Converts the input to camel case.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to camel case.
Examples
Example 1:
=tocamelcase("First Name")=>"firstName"Example 2:
=tocamelcase("Product Identifier")=>"productIdentifier"
ToClassCase
Formula
=toclasscase(<input>)
Description
Converts the input to class case.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to class case
Examples
Example 1:
=toclasscase("first name")=>"FirstName"Example 2:
=toclasscase("product identifier")=>"ProductIdentifier"
ToKebabCase
Formula
=tokebabcase(<input>)
Description
Converts the input to kebab case, replacing spaces between words with a "-" and converts all text to lower case.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to kebab case
Examples
Example 1:
=tokebabcase("First Name")=>"first-name"Example 2:
=tokebabcase("product description")=>"product-description"Example 3:
=tokebabcase("Product ID")=>"product-id"
ToLowerCase
Formula
=tolowercase(<input>)
Description
Converts the text input to lower case.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to lower case
Examples
Example 1:
=tolowercase("First Name")=>"first name"Example 2:
=tolowercase("Product Identifier")=>"product identifier"
ToScreamingSnakeCase
Formula
=toscreamingsnakecase(<input>)
Description
Converts the text input to screaming snake case by replacing all lowercase with uppercase and replacing spaces with underscores.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to screaming snake case
Examples
Example 1:
=toscreamingsnakecase("First Name")=>"FIRST_NAME"Example 2:
=toscreamingsnakecase("Product identifier")=>"PRODUCT_IDENTIFIER"
ToSentenceCase
Formula
=tosentencecase(<input>)
Description
Converts the text input to sentence case by replacing the first letter of the input text with an uppercase letter and replacing dashes or underscores with spaces.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to sentence case
Examples
Example 1:
=tosentencecase("first Name")=>"First name"Example 2:
=tosentencecase("PRODUCT_IDENTIFIER")=>"Product identifier"
ToSnakeCase
Formula
=tosnakecase(<input>)
Description
Converts the text input to snake case by replacing all uppercase with lowercase and replacing spaces with underscores.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to snake case
Examples
Example 1:
=tosnakecase("First NAME")=>"first_name"Example 2:
=tosnakecase("Product-ID")=>"product_id"
ToTitleCase
Formula
=totitlecase(<input>)
Description
Converts the text input to title case by replacing all first letters of words with uppercase and replacing any underscores and dashes with spaces.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to title case
Examples
Example 1:
=totitlecase(currentdatetime(, "the day of the week is %a")=>"The Day Of The Week Is Fri"Example 2:
=totitlecase("product_identifier")=>"Product Identifier"
ToTrainCase
Formula
=totraincase(<input>)
Description
Converts the text input to train case by replacing all first letters of words with uppercase and replacing any underscores and spaces with dashes.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to train case
Examples
Example 1:
=totraincase("First name")=>"First-Name"Example 2:
=totraincase("Product_identifier")=>"Product-Identifier"
ToUpperCase
Formula
=touppercase(<input>)
Description
Converts the text input to upper case.
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be converted to upper case
Examples
Example 1:
=touppercase("First Name")=>"FIRST NAME"Example 2:
=touppercase("Product-identifier")=>"PRODUCT-IDENTIFIER"
ToProperCase (ToProper)
Formula
=topropercase(<input>)
=toProper(<input>)
Description
Makes the first character of the input and any other characters that follow a non-letter character uppercase, and the rest of the characters lowercase.
Arguments
Input
Y
The input to be converted to proper case
Examples
Example 1:
=topropercase("first NAmE")=>"First Name"Example 2:
=topropercase(" 8Days U9il NN-L. ")=>"8Days U9Il Nn-L."
Trim
Formula
=trim(<input>)
Description
Trims whitespace around a string. Does not trim whitespace in between characters.
Arguments
input
Y
String to remove surrounding whitespace from
Examples
Example 1:
=trim(" taco ")=>"taco"Example 2:
=trim(" taco cat ")=>"taco cat"Example 3:
=trim("taco cat")=>"taco cat"
Truncate (Left)
Formula
=truncate(<input>, <number>)
=left(<input>, <number>)
Description
Truncates text input to a specific length cutting off excess characters from the end.
Left is used the same as truncate but more recognizable to Excel users
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be truncated
Number
Y
The number of digits from the end to truncate the Input to
Examples
Example 1:
=truncate("tacocat",4)=>"taco"
=left("tacocat",4)=>"taco"Example 2:
=truncate("Wed Mar 07 2018 19:57:53 GMT-0500 (Eastern Standard Time)", 33)=>"Wed Mar 07 2018 19:57:53 GMT-0500"
rTruncate (Right)
Formula
=rtruncate(<input>, <number>)
=right(<input>, <number>)
Description
Truncates a given input to a specific length, cutting off excess characters from the beginning.
Right is used the same as truncate but more recognizable to Excel users
Arguments
Argument
Required (Y/N)
Description
Input
Y
Text input to be truncated
Number
Y
The number of digits from the beginning to truncate the Input to
Examples
Example 1:
=rtruncate("tacocat", 4)=>"ocat"
=right("tacocat", 4)=>"ocat"Example 2:
=rtruncate($"Date", 4)
Delimit
Formula
=delimit(<input>, <delimiter>, <position>)
Description
Splits a string into an array of strings using the given delimiter. Returns the string at the given position in the array, starting at one. If the position is out of bounds, returns null.
Arguments
Input
Y
Text input to be split
Delimiter
Y
The character or string to split by
Position
Y
The index of the split output to take, starting at 1
Examples
Example 1:
=delimit("a,b,c", ",", 1)=>"a"Example 2:
=delimit("a,,,,b", ",", 2)=>""Example 3:
=delimit("a,b,c", ",", 5)=>(null)
Last updated