Text Formula Functions

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

The functions listed on this page are only available for Text Formulas. If you're looking for the functions available for Equations, see our article 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) = pie
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 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.

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

Link Functions

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

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

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

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