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.

This article covers the following topics:

 

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

 

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

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

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!

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 format 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 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 are not able to be extracted with the date formatter function.