Text Formula Functions

Text Functions

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

Updated 18 days ago