Advanced Formula: Text Functions

Jotform Tables’ Advanced Formula provides various functions for handling text values. This guide describes the formula syntax and usage of text functions available in Jotform Tables.

Here are the available text functions in Jotform Tables:

See also:

• Operators
• Logical Functions
• Numeric Functions
• Date Functions
• Array and Record Functions

FIND

Returns the numeric position of the first occurrence of needle in haystack. Unlike SEARCH, FIND is case-sensitive and can start the search from an optional offset.

Syntax:

FIND(needle,haystack[,offset])

Examples:

• =FIND("day","Sunday") will return 4.
• =FIND("Day","Sunday") will return VALUE!.
• =FIND("o","Hello World") will return 5.
• =FIND("o","Hello World",6) will return 8.

LEFT

Returns the portion of text from the left side, specified by the length parameter.

Syntax:

LEFT(text,length)

Examples:

• =LEFT("Sunday",3) will return Sun.
• =LEFT("Hello World",5) will return Hello.

LEN

Returns the length of text.

Syntax:

LEN(text)

Examples:

• =LEN("Sunday") will return 6.
• =LEN("Hello World") will return 11.

LOWER

Converts text to lowercase.

Syntax:

LOWER(text)

Examples:

• =LOWER("SUNDAY") will return sunday.
• =LOWER("Hello World") will return hello world.

MID

Returns the portion of text specified by the offset and length parameters.

Syntax:

MID(text,offset,length)

Examples:

• =MID("abcdef",2,3) will return bcd.
• =MID("Sunday",2,1) will return u.
• =MID("Hello World",2,4) will return ello.

REPLACE

Replaces a portion of text, specified by the offset and length parameters, with the given replace value.

Syntax:

REPLACE(text,offset,length,replace)

Examples:

• =REPLACE("Sunday",4,3,"") will return Sun.
• =REPLACE("Sunday",4,3,"spot") will return Sunspot.
• =REPLACE("Sunday",1,3,"") will return day.
• =REPLACE("Sunday",1,3,"Some") will return Someday.
• =REPLACE("Sunday",1,7,"Some") will return #VALUE!.

REPT

Repeats text by the specified number of times.

Syntax:

REPT(text,number)

Examples:

• =REPT("aye",2) will return ayeaye.
• =REPT("Make",2) will return MakeMake.
• =REPT("well, ",3) will return well, well, well, .

RIGHT

Returns the portion of text from the right side, specified by the length parameter.

Syntax:

RIGHT(text,length)

Examples:

• =RIGHT("Sunday",3) will return day.
• =RIGHT("Hello World",5) will return World.

SEARCH

Returns the numeric position of the first occurrence of needle in haystack. Unlike FIND, SEARCH is case-insensitive.

Syntax:

SEARCH(needle,haystack)

Examples:

• =SEARCH("day","Sunday") will return 4.
• =SEARCH("Day","Sunday") will return 4.
• =SEARCH("o","Hello World") will return 5.

SUBSTITUTE

Replace all occurrences of search in text with the given replace value. SUBSTITUTE is case-sensitive.

Syntax:

SUBSTITUTE(text,search,replace)

Examples:

• =SUBSTITUTE("Sunday","day","") will return Sun.
• =SUBSTITUTE("Sunday","Sun","Fri") will return Friday.
• =SUBSTITUTE("Apples and bananas","a","o") will return Apples ond bononos.

T

Returns value if it’s a string and blank otherwise.

Syntax:

T(value)

Examples:

• =T("Apples") will return Apples.
• =T(20) will return an empty string.
• =T("20.50") will return an empty string.
• =T(TRUE) will return an empty string.

TEXT

Changes the way value appears by applying format. The value parameter can either be a number or a date.

Syntax:

TEXT(value,format)

Here are some popular examples:

• =TEXT(12345.678,"$#,###.##") — A currency with thousands separator and two decimals, like $12,345.68. The value is rounded to the specified decimal places.
• =TEXT("October 20, 1990","MM/DD/YY") — Date in MM/DD/YY format, like 10/20/90.
• =TEXT(45678.91011,"YYYY-MM-DD HH:MM:SS") will return 2025-01-21 21:50:34.
• =TEXT("10/20/90","DDDD") — Date’s day of the week, like Saturday.
• =TEXT(0.345,"#.##%") — Percentage, like 34.50%.
• =TEXT(5.50 ,"# ?/?") — Fraction, like 5 1/2.
• =TEXT(123000000,"#.##E+#") — Scientific notation, like 1.23E+8.
• =TEXT(123456789,"(###) ###-####") — Phone number, like (012) 345-6789.
• =TEXT(23,"####") — Leading zeroes, like 0023.

For related information, see Excel: TEXT function.

TEXTJOIN

Combines text items with delimiter. Empty text items are ignored as long as the ignore_empty parameter is not FALSE (bool).

Syntax:

TEXTJOIN(delimeter,ignore_empty,text1[,text2,...]

Examples:

• =TEXTJOIN("-","true","merry"," ","go"," ","round"," ") will return merry-go-round.
• =TEXTJOIN("+","false","merry","","go","","round") will return merry+go+round.
• =TEXTJOIN("*",FALSE(),"merry","","go","","round") will return merry**go**round.

TRIM

Removes all spaces from text except for single spaces between words.

Syntax:

TRIM(text)

Examples:

• =TRIM("   Sunday   ") will return Sunday.
• =TRIM("   Hello   World   ") will return Hello World.

UPPER

Converts text to uppercase.

Syntax:

UPPER(text)

Examples:

• =UPPER("Sunday") will return SUNDAY.
• =UPPER("Hello World") will return HELLO WORLD.