# 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**

| Argument      | Required (Y/N) | Description                                                           |
| ------------- | -------------- | --------------------------------------------------------------------- |
| 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")` => `0`
>
> **Example 2:**
>
> `=find("tacocat", "cat")` => `4`
>
> **Example 3:**
>
> `=find("tacocat", "dog")` => `null`
>
> **Example 4:**
>
> `=find("tacocat", "taco", 2)` => `null`
>
> **Example 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

| Argument      | Required (Y/N) | Description                                                           |
| ------------- | -------------- | --------------------------------------------------------------------- |
| 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")` => `7`
>
> **Example 2:**
>
> `=rfind("abc xyz abc", "abc", 5)` => `9`
>
> **Example 3:**
>
> `=rfind("abc xyz", "abc", 5)` => (`null)`

## **Len**

**Formula**

`=len(<input>)`

**Description**

Returns the length of a given input.

**Arguments**

| Argument | Required (Y/N) | Description                 |
| -------- | -------------- | --------------------------- |
| input    | Y              | Input to find the length of |

**Examples**

> **Example 1:**
>
> `=len("tacocat")` => `7`
>
> **Example 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**

| Argument    | Required (Y/N) | Description                                              |
| ----------- | -------------- | -------------------------------------------------------- |
| 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:**&#x20;
>
> `=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

| Argument | Required (Y/N) | Description                                              |
| -------- | -------------- | -------------------------------------------------------- |
| 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]

{% hint style="info" %}
Skipping a row means the row will not be included in the output.
{% endhint %}

## 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"`

## To**Title**Case

#### 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.&#x20;

#### 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.&#x20;

#### Arguments

<table data-header-hidden><thead><tr><th width="240.33333333333326">Argument</th><th>Required (Y/N)</th><th>Description</th></tr></thead><tbody><tr><td>Argument</td><td>Required (Y/N)</td><td>Description</td></tr><tr><td>Input</td><td>Y</td><td>Text input to be converted to upper case</td></tr></tbody></table>

#### 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

| Argument | Required (Y/N) | Description                              |
| -------- | -------------- | ---------------------------------------- |
| 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 <a href="#truncate" id="truncate"></a>

**Formula**

`=trim(<input>)`

**Description**

Trims whitespace around a string. Does not trim whitespace in between characters.

**Arguments**

| Argument | Required (Y/N) | Description                                  |
| -------- | -------------- | -------------------------------------------- |
| input    | Y              | String to remove surrounding whitespace from |

**Examples**

> **Example 1:**
>
> `=trim(" taco ")` => `"taco"`
>
> **Example 2:**
>
> `=trim(" taco cat ")` => `"taco cat"`
>
> &#x20;**Example 3:**
>
> `=trim("taco cat")` => `"taco cat"`

## Truncate (Left) <a href="#truncate" id="truncate"></a>

#### 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

<table data-header-hidden><thead><tr><th width="228.33333333333326">Argument</th><th>Required (Y/N)</th><th>Description</th></tr></thead><tbody><tr><td>Argument</td><td>Required (Y/N)</td><td>Description</td></tr><tr><td>Input</td><td>Y</td><td>Text input to be truncated</td></tr><tr><td>Number</td><td>Y</td><td>The number of digits from the end to truncate the Input to</td></tr></tbody></table>

#### 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) <a href="#truncate" id="truncate"></a>

#### 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

<table data-header-hidden><thead><tr><th width="271.8196286472148">Argument</th><th>Required (Y/N)</th><th>Description</th></tr></thead><tbody><tr><td>Argument</td><td>Required (Y/N)</td><td>Description</td></tr><tr><td>Input</td><td>Y</td><td>Text input to be truncated</td></tr><tr><td>Number</td><td>Y</td><td>The number of digits from the beginning to truncate the Input to</td></tr></tbody></table>

#### 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

| Argument  | Required (Y/N) | Description                                          |
| --------- | -------------- | ---------------------------------------------------- |
| 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)`