Table of Contents

Text Formula Functions

Lesley Updated by Lesley

This information is referenced in the Text Formulas article.

The functions listed on this page are only available for Text Formulas. If you're looking for the functions available for Equations, click here.

Text Functions

Note that all Text Formulas which use text strings are case-sensitive; "abc" is not the same as “ABC”.

Name

Function with Arguments

Description

Example with Result

left

left(String, End Position)

The first characters in a text string, based on the number of characters specified

left(Sample, 3) = Sam

right

right(String, End Position)

The last characters in a text string, based on the number of characters specified

right(Sample, 3) = ple

mid

mid(String, Start Position, Number of Characters)

Extract a sub-string of a string, starting at the Start Position (count from zero from the left side) and ending at Number of Characters to the right of it (including itself)

mid(Sample, 2, 4) = amp

trim

trim( {Name} )

Remove white space both left and right of a variable

trim( Smith ) = Smith

trimLeft

trimLeft( {Name})

Remove whitespace left of a variable

trimLeft( Smith ) = "Smith "

trimRight

trimRight({Name} )

Remove whitespace right of a variable

trimRight( Smith ) = " Smith"

length

length(String)

Get the number of characters in a text string

length(Sample) = 6

replace

replace(String, Search Value, Replace Value)

Replace a value found in a string with another value

replace(ABCabc123, ABC, 123) = 123abc123

lower

lower(String)

Convert a text string to lower case

lower(SaMpLe) = sample

upper

upper(String)

Convert a text string to upper case

upper(SaMpLe) = SAMPLE

capitalize

capitalize(String)

Capitalize the first letter of a string

capitalize(sample) = Sample

random

random(Length)

Output a random string containing the Length argument number of upper-case letters, lower-case letters, and numbers

random(length(String)) = h0bFit

numberToWords

numberToWords(String)

Convert a number to it's written form

numberToWords(110) = one hundred ten

Regex Functions

Regex falls outside the scope of our support. This means that we’re unable to help further with setup or troubleshooting. Additionally, with a code-based solution, we can’t guarantee its functionality or full compatibility with Knack.

For resources to help with regex, read more here.

regexReplace

regexReplace(String, Regular Expression Search, Replace Value)

Replace a regular expression value found in a string with another value

regexReplace(Sample, [A-Z], 9999) = 9999ample

extractRegex

extractRegex(String, Regular Expression Search)

Extract the first matching regular expression from a string

extractRegex(SAMPLE, [A-Z]) = S

Address Functions

Address Field data used in examples below: 123 Main St., Apt. 2, Amonate, VA 24601, United States

Name

Function with Arguments

Description

Example with Result

getAddressStreet

getAddressStreet(ADDRESS)

Extract the street from an Address Field

getAddressStreet({Address}) = 123 Main St.

getAddressStreet2

getAddressStreet2(ADDRESS)

Extract the street2 from an Address Field

getAddressStreet2({Address}) = Apt. 2

getAddressCity

getAddressCity(ADDRESS)

Extract the city an Address Field

getAddressCity({Address}) = Amonate

getAddressState

getAddressState(ADDRESS)

Extract the state from an Address Field

getAddressState({Address}) = VA

getAddressZip

getAddressZip(ADDRESS)

Extract the zip code an Address Field

getAddressZip({Address}) = 24601

getAddressCountry

getAddressCountry(ADDRESS)

Extract the country from an Address Field

getAddressCountry({Address}) = United States

Name Functions

Name Field data used in examples below: Dr. Jane Rose Doe

Name

Function with Arguments

Description

Example with Result

getNameTitle

getNameTitle(NAME)

Extract the title from a Name Field

getNameTitle({Name}) = Dr.

getNameFirst

getNameFirst(NAME)

Extract the first name from a Name Field

getNameFirst({Name}) = Jane

getNameMiddle

getNameMiddle(NAME)

Extract the middle name from a Name Field

getNameMiddle({Name}) = Rose

getNameLast

getNameLast(NAME)

Extract the last name from a Name Field

getNameLast({Name}) = Doe

Sample Link Field data: URL: https://www.example.com, Label: Example!

Name

Function with Arguments

Description

Example with Result

getLinkURL

getLinkURL(LINK)

Extract the URL from a Link Field

getLinkURL({Link}) = https://www.example.com

getLinkLabel

getLinkLabel(LINK)

Extract the label from a Link Field

getLinkLabel({Link}) = Example!

Date Functions

Sample Date Field data: April 15, 2017

For additional date functions, take a look at our Equation formulas which you can use to get the day, week, month, quarter, or year from a Date Field.

Name

Function with Arguments

Description

Example with Result

getDateDayOfWeekName

getDateDayOfWeekName(DATE)

Get the day of the week

getDateDayOfWeekName({Date}) = Friday

getDateMonthOfYearName

getDateMonthOfYearName(DATE)

Get the month of the year

getDateMonthOfYearName({Date}) = April

Date Formatter

The date formatter function allows you to transform date fields into various outputs. 

Name

Function with Arguments

Description

Example with Result

formatDate

formatDate(DATE, Output Format)

Transforms the date into defined output format

formatDate({Date}, YYYY-MM-DD) = 2018-06-13

Date Formatter Output Options

The available date output options are listed below:

 Year, month, and day tokens

Input

Example

Description

YYYY

2014

4 digit year

YY

14

2 digit year

Y

-25

Year with any number of digits and sign

Q

1..4

Quarter of year. Sets month to first month in quarter.

M MM

1..12

Month number

MMM MMMM

Jan..December

Month name in locale set by moment.locale()

D DD

1..31

Day of month

Do

1st..31st

Day of month with ordinal

DDD DDDD

1..365

Day of year

X

1410715640.579

Unix timestamp

x

1410715640579

Unix ms timestamp

 

Week year, week, and weekday tokens

Input

Example

Description

gggg

2014

Locale 4 digit week year

gg

14

Locale 2 digit week year

w ww

1..53

Locale week of year

e

0..6

Locale day of week

ddd dddd

Mon...Sunday

Day name in locale set by moment.locale()

GGGG

2014

ISO 4 digit week year

GG

14

ISO 2 digit week year

W WW

1..53

ISO week of year

E

1..7

ISO day of week

 

Hour, minute, second, millisecond, and offset tokens

Input

Example

Description

H HH

0..23

Hours (24 hour time)

h hh

1..12

Hours (12 hour time used with a A.)

k kk

1..24

Hours (24 hour time from 1 to 24)

a A

am pm

Post or ante meridiem (Note the one character a p are also considered valid)

m mm

0..59

Minutes

s ss

0..59

Seconds

S SS SSS

0..999

Fractional seconds

Z ZZ

+12:00

Offset from UTC as +-HH:mm, +-HHmm, or Z

Currently end date/time values are not able to be extracted with the date formatter function.

How did we do?

About Fields

Contact