Text Formula Functions

This article provides a comprehensive list of commonly used text formula functions.

The functions listed in this article are only available for text formulas. If you're looking for the functions available for equations, see the following articles:

Numeric Equation Functions
Date Equation Functions

This article covers the following topics:

Text Functions
Regex Functions
Address Functions
Name Functions
Link Functions
Date Functions
Date Formatter
Date Formatter Output Options
Week Year, Week, & Weekday Tokens
Hour, Minute, Second, Millisecond, & Offset Tokens

Text Functions

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

Function 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 the Number of Characters to the right of it (including itself)	`mid(Sample, 2, 4)` = amp
`trim`	`trim`( {Name} )	Remove the white space from 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 lowercase	`lower(SaMpLe)` = sample
`upper`	`upper`(String)	Convert a text string to uppercase	`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 its written form	`numberToWords(110)` = one hundred ten

Regex Functions

Note: Please note that regex falls outside the scope of our support. We are unable to provide assistance with setup or troubleshooting related to regex.

Additionally, since it involves code-based solutions, we cannot guarantee its functionality or full compatibility with Knack.

For resources to help with regex, learn more here.

Function Name	Function with Arguments	Description	Example with Result
`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 the examples below:

123 Main St., Apt. 2, Amonate, VA 24601, United States

Function 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 street 2 from an address field	`getAddressStreet2({Address})` = Apt. 2
`getAddressCity`	`getAddressCity`(ADDRESS)	Extract the city from 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 from an address field	`getAddressZip({Address})` = 24601
`getAddressCountry`	`getAddressCountry`(ADDRESS)	Extract the country from an address field	`getAddressCountry({Address})` = United States

Name Functions

Person field data used in examples below: Dr. Jane Rose Doe

Function Name	Function with Arguments	Description	Example with Result
`getNameTitle`	`getNameTitle`(NAME)	Extract the title from a person field	getNameTitle({Name}) = Dr.
`getNameFirst`	`getNameFirst`(NAME)	Extract the first name from a person field	getNameFirst({Name}) = Jane
`getNameMiddle`	`getNameMiddle`(NAME)	Extract the middle name from a person field	getNameMiddle({Name}) = Rose
`getNameLast`	`getNameLast`(NAME)	Extract the last name from a person field	getNameLast({Name}) = Doe

Link Functions

Sample link field data: URL: https://www.example.com, Label: Example!

Function 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

Note: 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.

Sample date field data: April 15, 2017

Function 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 format function allows you to transform date fields into various outputs.

Function Name	Function with Arguments	Description	Example with Result
`formatDate`	`formatDate`(DATE, Output Format)	Transforms the date into the 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, & 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, & 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

Note: Currently, end date/time values cannot be extracted with the date formatter function.