Data Transformation – Available Functions

The Formula Editor includes built-in functions grouped into categories:

• Text
• Number
• Date
• Table

Every function is also available in the Formula Editor: as a recipe in Basic or as a button in Advanced. The table, lookup and build functions are on Lookup, list and build functions.

Below is an overview of all available functions grouped by category.

Text Functions

Number Functions

Date Functions

Table Functions

TextFormat

What does this function do?

TextFormat returns a text value. You can also change its letter case.

It can convert text to:

• Uppercase
• Lowercase
• Title case (first letter of each word capitalized)

Syntax

TextFormat({{variable}})
TextFormat({{variable}}, "upper" | "lower" | "title")

• {{variable}} – The text you want to format
• The mode is optional. Without it, the value is returned unchanged.
• "upper" – ALL LETTERS
• "lower" – all letters
• "title" – First Letter Of Each Word

Examples

No mode

TextFormat({{city}})

If city = "Oslo" → Oslo

Uppercase

TextFormat({{firstName}}, "upper")

If firstName = "anna" → ANNA

Lowercase

TextFormat({{email}}, "lower")

If email = "INFO@COMPANY.COM" → info@company.com

Title case

TextFormat({{fullName}}, "title")

If fullName = "anna svensson" → Anna Svensson

Important

• Only works with text values.
• Returns Text.
• Placeholder variable must exist.

TextTrim

What does this function do?

TextTrim removes spaces at the beginning and end of a text value.

It does not remove spaces inside the text — only before and after.

Syntax

TextTrim({{variable}})

• {{variable}} – The text you want to clean

Example

TextTrim({{customerName}})

If: customerName = " Anna Svensson "

Result: Anna Svensson

Important

• Only removes leading and trailing spaces.
• Does not affect spaces between words.
• Returns Text.
• The variable must contain text.

TextLen

What does this function do?

TextLen returns the number of characters in a text value.

It counts all characters, including letters, numbers, and spaces.

Syntax

TextLen({{variable}})

• {{variable}} – The text you want to measure

Example

TextLen({{customerName}})

If: customerName = "Anna Svensson"

Result: 13

(Spaces are included in the count.)

Important

• Counts all characters, including spaces.
• Returns a Number.

TextReplace

What does this function do?

TextReplace replaces part of a text value with another value.

It searches for a specific text and replaces all matching occurrences.

Syntax

TextReplace({{variable}}, "search", "replace")

• {{variable}} – The text to modify
• "search" – The text you want to find
• "replace" – The text that will replace it

Example

TextReplace({{phoneNumber}}, "-", "")

If: phoneNumber = "070-123-45-67"

Result: 0701234567

Important

• Replaces all matching occurrences.
• Search is case-sensitive.
• Returns Text.
• The variable must contain text.

TextJoin

What does this function do?

TextJoin combines multiple values into one single text string.

It joins values in the order you write them.

Syntax

TextJoin(value1, value2, value3, ...)

• value1, value2, ... – Text values, variables, or static text
• Values are joined exactly in the order provided

Example

TextJoin({{firstName}}, " ", {{lastName}})

If:

• firstName = "Anna"
• lastName = "Svensson"

Result: Anna Svensson

Example – Add Static Text

TextJoin("Order: ", {{orderId}})

If:

• orderId = 12345

Result: Order: 12345

Important

• Values are joined exactly as written.
• You must manually add spaces or separators (for example " " or ", ").
• Returns Text.

TextSubstring

What does this function do?

TextSubstring extracts part of a text value based on position.

It returns a selected portion of the text.

Syntax

TextSubstring({{variable}}, startIndex, endIndex)

• {{variable}} – The text to extract from
• startIndex – Position where extraction starts (included)
• endIndex – Position where extraction ends (not included)

Index starts at 0 (first character).

Example

TextSubstring({{ssn}}, 0, 6)

If: ssn = "19900101-1234"

Result: 199001

(Starts at position 0 and stops before position 6, so it returns 6 characters.)

Example – Extract Last 4 Characters

TextSubstring({{ssn}}, 9, 13)

If: ssn = "19900101-1234"

Result: 1234

Important

• Index starts at 0.
• The end position is not included, so the number of characters returned is endIndex - startIndex.
• If the start position is not before the end position, or is past the end of the text, the result is empty.
• Returns Text.

NumberFormat

What does this function do?

NumberFormat formats a number into a readable text format.

It allows you to control decimals and thousand separators.

Syntax

NumberFormat({{variable}}, "format")

• {{variable}} – The number you want to format
• "format" – Defines how the number should be displayed

The format string controls:

• Thousand separator
• Decimal separator
• Number of decimals

Example

NumberFormat({{amount}}, "1 000,00")

If: amount = 1234.5

Result: 1 234,50

Example – No Decimals

NumberFormat({{amount}}, "1 000")

If: amount = 1234.5

Result: 1 235

Example – Standard Formats N and F

You can also use N or F followed by the number of decimals. N puts a comma between thousands and a point before the decimals. F uses no thousand separator.

NumberFormat({{amount}}, "N2")

If: amount = 12345.678

Result: 12,345.68

With "F2" the result is 12345.68.

Important

• The function returns Text (not Number).
• The format must match your regional number style.
• Only works with numeric values.

DateFormat

What does this function do?

DateFormat formats a date into a specific text format.

You can control both the date pattern and the language/culture.

Syntax

DateFormat({{variable}}, "format", "culture")

• {{variable}} – The date value to format
• "format" – Defines how the date should be displayed
• "culture" – Language/culture code (for example "nb")

Example

DateFormat({{invoiceDate}}, "dd.MM.yyyy", "nb")

If: invoiceDate = 2025-06-15

Result: 15.06.2025

Example – With Month Name

DateFormat({{invoiceDate}}, "dd MMMM yyyy", "nb")

If: invoiceDate = 2025-06-15

Result: 15 juni 2025

Important

• The function returns Text.
• The format must follow standard date format patterns.
• MM is the month and mm is minutes. When the format has no time part (no hours, seconds, fractions of a second, AM/PM or time zone), mm is read as the month, so "dd.mm.yyyy" gives the same result as "dd.MM.yyyy".
• The culture code controls month and weekday language.
• The variable must contain a valid date.

DateAddDays / DateAddMonths / DateAddYears

What do these functions do?

These functions add or subtract time from a date.

• DateAddDays – Adds days
• DateAddMonths – Adds months
• DateAddYears – Adds years

Use positive numbers to add time.
Use negative numbers to subtract time.

Syntax

DateAddDays({{date}}, number)
DateAddMonths({{date}}, number)
DateAddYears({{date}}, number)

• {{date}} – The original date
• number – Amount of time to add (can be negative)

Example – Add 14 Days

DateAddDays({{startDate}}, 14)

If: startDate = 2025-06-01

Result: 2025-06-15

Example – Add 2 Months

DateAddMonths({{startDate}}, 2)

If: startDate = 2025-01-10

Result: 2025-03-10

Example – Add 1 Year

DateAddYears({{startDate}}, 1)

If: startDate = 2025-06-01

Result: 2026-06-01

Important

• All functions return a Date.
• Use DateFormat() if you need formatted text output.
• If a month has fewer days (for example February), the date adjusts automatically.

DateDiff

What does this function do?

DateDiff calculates the difference between two dates.

You can choose whether the result should be returned in days, months, or years.

Syntax

DateDiff({{startDate}}, {{endDate}}, "unit")

• {{startDate}} – The first date
• {{endDate}} – The second date
• "unit" – The unit of time:
  • "days"
  • "months"
  • "years"

Example – Difference in Days

DateDiff({{startDate}}, {{endDate}}, "days")

If:

• startDate = 2025-06-01
• endDate = 2025-06-15

Result: 14

Example – Calculate Age in Years

DateDiff({{birthDate}}, {{today}}, "years")

If:

• birthDate = 2000-05-10
• today = 2025-05-10

Result: 25

Important

• Returns a Number.
• Days count whole days. Months and years count calendar boundaries, not completed months or years. For example, from 2024-12-31 to 2025-01-01 is 1 year and 1 month.
• This means an age calculated in years is one too high until the birthday, so check the result before you use it as an age.
• The result is negative when the end date is before the start date.
• Both variables must contain valid dates.

DateStartOfMonth / DateEndOfMonth

What do these functions do?

These functions return the first or last day of the month for a given date.

• DateStartOfMonth → First day of the month
• DateEndOfMonth → Last day of the month

Syntax

DateStartOfMonth({{date}})
DateEndOfMonth({{date}})

• {{date}} – The original date

Example

If: invoiceDate = 2025-06-15

Start of month

DateStartOfMonth({{invoiceDate}})

Result: 2025-06-01

End of month

DateEndOfMonth({{invoiceDate}})

Result: 2025-06-30

Important

• Both functions return a Date.
• The original day value is ignored.
• Use DateFormat() if you need formatted text output.

DateToday

What does this function do?

DateToday returns today’s date.

Syntax

DateToday()
DateToday("format")

The format is optional.

Example – No Format

DateToday()

If today is 24 February 2026

Result: 2026-02-24

Without a format, or with an empty format, the date is returned as yyyy-MM-dd.

Example – Formatted Output

DateToday("dd.MM.yyyy")

If today is 24 February 2026

Result: 24.02.2026

Important

• Format must follow standard date format patterns.

TableCount

What does this function do?

TableCount returns the number of rows in a table variable.

Use it to check how many records exist in a table.

Syntax

TableCount({{tableName}})

• {{tableName}} – The table variable

Example – Count Rows

TableCount({{employees}})

If the table employees contains 5 rows,

Result: 5

Example – Use in a Condition

You want to check if the table contains any rows.

TableCount({{employees}})

If the result is greater than 0, the table contains data.

Important

• Returns a Number.
• Only works with table variables.
• If the table is empty, the result is 0.

TableMerge

What does this function do?

TableMerge combines values from a specific column in a table into a single text string.

You can define:

• A separator between values
• A different separator before the last value

Syntax

TableMerge({{tableName}}, "fieldName", "separator", "lastSeparator")

• {{tableName}} – The table variable
• "fieldName" – The column to extract values from
• "separator" – Optional. Separator between values. The default is ", "
• "lastSeparator" – Optional. Separator before the last value. The default is " og " (Norwegian for “and”)

Always enter both separators so the result does not depend on the defaults.

Example

TableMerge({{products}}, "name", ", ", " and ")

If the table products contains:

Result:

Apple, Banana and Orange

Important

• Returns Text.
• Only works with table variables.
• If the table contains one row, no separators are added.
• If the table is empty, the result is empty.

TableMap

What does this function do?

TableMap builds a readable text string from a table.

It applies a template to each row and combines the results into one sentence.

Syntax

TableMap({{tableName}}, "template", "separator", "lastSeparator")

• {{tableName}} – The table variable
• "template" – Text applied to each row (use {{columnName}} to insert column values)
• "separator" – Optional. Separator between rows. The default is ", "
• "lastSeparator" – Optional. Separator before the last row. The default is " och " (Swedish for “and”)

Always enter both separators so the result does not depend on the defaults.

Example – Insurance Policies

TableMap({{InsurancePolicies}}, "{{policyType}}, {{coverageAmount}} kr", ", ", " and ")

If the table contains:

[
  { "policyType": "Home Insurance", "coverageAmount": 2000000 },
  { "policyType": "Car Insurance", "coverageAmount": 500000 },
  { "policyType": "Travel Insurance", "coverageAmount": 100000 }
]

Result:

Home Insurance, 2000000 kr, Car Insurance, 500000 kr and Travel Insurance, 100000 kr

Important

• Returns Text.
• {{columnName}} must match the table column exactly.
• The function automatically loops through all rows.
• If the table is empty, the result is empty.