Unqork Formulas are functions added to Unqork's formula engine that do not exist in standard Excel. Use them for date manipulation, data parsing, encoding, regex operations, number formatting, and working with arrays and objects.
For the full list of all supported formulas, see Formulas.
Date & Time
DATEFORMAT
Formats a date value into a readable string.
Syntax: DATEFORMAT(date, format)
Argument | Description |
|---|---|
| A date string or field reference containing a date. Does not accept Unix timestamps. |
| A format string using Moment.js tokens. See Moment.js Formulas. |
Example:
=DATEFORMAT(submittedDate, "MM/DD/YYYY")
Returns 03/25/2026 when submittedDate is 2026-03-25.
DATEADD
Adds a specified amount of time to a date.
Syntax: DATEADD(date, amount, unit)
Argument | Description |
|---|---|
| A date string or field reference. |
| A number indicating how much time to add. Use a negative number to subtract. |
| The unit of time: |
Example:
=DATEADD(startDate, 30, "days")
=DATEADD(expiryDate, -1, "years")
MOMENT
Parses a date string using a specified input format and returns a date value. Use MOMENT() when you need to parse a date stored in a non-standard format before passing it to other formulas.
Syntax: MOMENT(date, inputFormat)
Argument | Description |
|---|---|
| A date string to parse. |
| The format of the input date string, using Moment.js tokens. |
Example:
=MOMENT("03/25/2026", "MM/DD/YYYY")
Returns a date value representing March 25, 2026.
See Moment.js Formulas for format token reference.
Text & Strings
SPLIT
Splits a string into an array using a delimiter.
Syntax: SPLIT(string, delimiter)
Example:
=SPLIT("red,green,blue", ",")
Returns ["red", "green", "blue"].
HTML2TEXT
Strips HTML tags from a string, returning plain text.
Syntax: HTML2TEXT(html)
Example:
=HTML2TEXT("<b>Hello</b> World")
Returns Hello World.
WORDWRAP
Wraps a string at a specified character width, returning the wrapped text as a single string.
Syntax: WORDWRAP(string, width)
WORDWRAPARRAY
Wraps a string at a specified character width, returning each line as an element in an array.
Syntax: WORDWRAPARRAY(string, width)
STRINGIFY
Converts a value to its string representation.
Syntax: STRINGIFY(value)
Example:
=STRINGIFY(fieldValue)
Useful when passing a non-text value to a field that expects a string.
NUMBERS
Filters the provided arguments and returns only the numeric values as an array.
Syntax: NUMBERS(value1, value2, ...)
PHONENUMBER
Formats a phone number string according to a locale.
Syntax: PHONENUMBER(number, locale)
Example:
=PHONENUMBER(phoneField, "US")
SPELLNUMBER
Converts a number to its spelled-out English representation.
Syntax: SPELLNUMBER(number)
Example:
=SPELLNUMBER(1250)
Returns one thousand two hundred fifty.
ALPHARANGE
Returns an array of consecutive letters between a start and end letter.
Syntax: ALPHARANGE(start, end)
Example:
=ALPHARANGE("A", "E")
Returns ["A", "B", "C", "D", "E"].
Numbers
NUMERAL
Formats a number using Numeral.js format strings.
Syntax: NUMERAL(number, format)
Format | |
|---|---|
|
|
|
|
|
|
|
|
|
|
Example:
=NUMERAL(totalAmount, "0,0.00")
Regex
REGEXMATCH
Returns true if a string matches a regular expression pattern, false if it does not.
Syntax: REGEXMATCH(string, pattern)
Example:
=REGEXMATCH(zipCode, "^\d{5}$")
Returns true for a valid 5-digit zip code. See Regular Expression Patterns.
REGEXEXTRACT
Extracts the first match of a regular expression pattern from a string.
Syntax: REGEXEXTRACT(string, pattern)
Example:
=REGEXEXTRACT(referenceNumber, "[0-9]+")
Returns the first sequence of digits found in the string.
REGEXREPLACE
Replaces matches of a regular expression pattern in a string with a replacement value.
Syntax: REGEXREPLACE(string, pattern, replacement)
Example:
=REGEXREPLACE(phoneField, "[^0-9]", "")
Strips all non-numeric characters from a phone number field.
Encoding & Parsing
BASE64/TOBASE64
Encodes a string to Base64 format. BASE64 and TOBASE64 are equivalent.
Syntax: BASE64(string) or TOBASE64(string)
Example:
=BASE64("hello")
Returns aGVsbG8=.
FROMBASE64
Decodes a Base64-encoded string back to plain text.
Syntax: FROMBASE64(string)
Example:
=FROMBASE64("aGVsbG8=")
Returns hello.
JSONPARSE
Parses a JSON string and returns the resulting object or array.
Syntax: JSONPARSE(string)
URLPARSE
Parses a URL string and returns an object containing its parts: protocol, host, hostname, port, pathname, search, query, hash, and href.
Syntax: URLPARSE(url)
Example:
=URLPARSE("https://example.com/path?foo=bar")
Returns an object. Use GET to access individual parts:
=GET(URLPARSE(urlField), "hostname")
Returns example.com.
LINKHEADERPARSE
Parses an HTTP Link header string and returns the links as a structured object.
Syntax: LINKHEADERPARSE(header)
Arrays & Collections
COUNTUNIQUE
Returns the count of unique values across the provided arguments.
Syntax: COUNTUNIQUE(value1, value2, ...)
Example:
=COUNTUNIQUE(status1, status2, status3, status4)
UNIQUE
Returns an array of unique values from the provided arguments.
Syntax: UNIQUE(value1, value2, ...)
COUNTIN
Counts how many times a value displays in a collection.
Syntax: COUNTIN(collection, value)
FLATTEN
Flattens a nested array into a single-level array.
Syntax: FLATTEN(array)
GROUPBY
Groups an array of objects by a specified key, returning an object where each key maps to an array of matching items.
Syntax: GROUPBY(array, key)
Example:
=GROUPBY(submissionsArray, "status")
Returns { "Active": [...], "Closed": [...] }.
JOIN
Joins all elements of an array into a single string using a separator.
Syntax: JOIN(array, separator)
Example:
=JOIN(nameArray, ", ")
Returns "Alice, Bob, Carol" when nameArray is ["Alice", "Bob", "Carol"].
JOINKEYVALUE
Joins key-value pairs from a flat object into a formatted string. The object must have parallel keys that share a common label prefix and value prefix—for example, label1/value1, label2/value2. The function matches keys by prefix and joins each label-value pair with a separator.
Syntax: JOINKEYVALUE(object, labelPrefix, valuePrefix, [keyValueSeparator], [pairSeparator])
Argument | Description |
|---|---|
| A flat object with parallel label and value keys sharing a common prefix. |
| The prefix identifying label keys. Defaults to |
| The prefix identifying value keys. Defaults to |
| Optional. The string placed between each label and value. Defaults to |
| Optional. The string placed between each pair. Defaults to |
Example:
Given a field containing { "label1": "Status", "value1": "Active", "label2": "Type", "value2": "Request" }:
=JOINKEYVALUE(myField, "label", "value")
Returns "Status: Active; Type: Request".
JOINKEYVALUEARRAY
Applies JOINKEYVALUE across an array of flat objects, returning an array of objects where each key is a specified field value from the source item.
Syntax: JOINKEYVALUEARRAY(array, displayKey, [labelPrefix], [valuePrefix], [keyValueSeparator], [pairSeparator])
Argument | Description |
|---|---|
| An array of flat objects with parallel label and value keys. |
| The field in each object to use as the key in the output. |
| Optional. Defaults to |
| Optional. Defaults to |
| Optional. Defaults to |
| Optional. Defaults to |
Example:
Given a field containing [{ "id": "row1", "label1": "Status", "value1": "Active" }, { "id": "row2", "label1": "Type", "value1": "Request" }]:
=JOINKEYVALUEARRAY(myField, "id", "label", "value")
Returns [{ "row1": "Status: Active" }, { "row2": "Type: Request" }].
LABELVALUEARRAY
Takes two parallel arrays and zips them into an array of { label, value } objects. Commonly used for building drop-down option lists from separate label and value arrays.
Syntax: LABELVALUEARRAY(labelArray, valueArray, [labelKey], [valueKey])
Argument | Description |
|---|---|
| An array of label values. |
| An array of corresponding values. |
| Optional. The key name for the label field. Defaults to |
| Optional. The key name for the value field. Defaults to |
Example:
=LABELVALUEARRAY(["Red", "Blue", "Green"], ["red", "blue", "green"])
Returns [{ "label": "Red", "value": "red" }, { "label": "Blue", "value": "blue" }, { "label": "Green", "value": "green" }].
KEYSARRAY
Takes a 2D array of values and an array of key names, and builds an array of objects by mapping each key name to a column in the 2D array.
Syntax: KEYSARRAY(dataArray, keysArray)
Argument | Description |
|---|---|
| A 2D array where each inner array is a column of values. |
| An array of key names corresponding to each column. |
Example:
=KEYSARRAY([["Alice", "Bob"], [25, 30]], ["name", "age"])
Returns [{ "name": "Alice", "age": 25 }, { "name": "Bob", "age": 30 }].
INITIAL
Returns the first N elements of an array.
Syntax: INITIAL(array, count)
Argument | Description |
|---|---|
| The source array. |
| The number of elements to return from the beginning. |
Example:
=INITIAL(myArray, 3)
Returns the first 3 elements of myArray.
REST
Returns all elements of an array starting from a specified index.
Syntax: REST(array, startIndex)
Argument | Description |
|---|---|
| The source array. |
| The index to begin from. Use |
Example:
=REST(myArray, 1)
Returns all elements of myArray except the first.
ARGS2ARRAY
Converts individual arguments into an array.
Syntax: ARGS2ARRAY(value1, value2, ...)
Object Access
GET
Returns the value of a specified key from an object.
Syntax: GET(object, key)
Example:
=GET(URLPARSE(urlField), "hostname")
=GET(currentUser, "email")
Utilities
LODASH
Provides access to Lodash utility functions. Pass the Lodash method name as the first argument, followed by the value to operate on.
Syntax: LODASH(method, value)
Common methods:
Method | Description | Example |
|---|---|---|
| Capitalizes the first letter |
|
| Converts to lowercase |
|
| Converts to uppercase |
|
| Removes leading/trailing whitespace |
|
| Converts to camelCase |
|
| Converts to snake_case |
|
| Converts to kebab-case |
|
For the full list of Lodash methods, see the Lodash documentation.
REFERENCE
References a value from in the module's submission data.
Syntax: REFERENCE(path)
ISBINARY
Returns true if a value is a binary string.
Syntax: ISBINARY(value)
INTERVAL
Converts a number of seconds into an ISO 8601 duration string.
Syntax: INTERVAL(seconds)
Argument | Description |
|---|---|
| A number of seconds to convert into a duration. |
Example:
=INTERVAL(3661)
Returns PT1H1M1S (1 hour, 1 minute, 1 second).
Changelog
Date | Change |
|---|---|
— | Initial publication. |