Advanced Formula: Text Functions

July 30, 2022

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’s the list of text functions you can use in Jotform Tables:

Function Description
FIND Finds an occurrence of characters in a string, starting at the starting position you specify.
LEFT Extract characters from the left side of a string.
LEN Returns the length of a string.
LOWER Makes a string lowercase.
MID Extract characters from a string, starting at the position you specify.
REPLACE Replaces the specified number of characters in a string with a new set, starting at the position you specify.
REPT Repeats characters or strings a given number of times.
RIGHT Extract characters from the right side of a string.
SEARCH Finds an occurrence of characters in a string.
SUBSTITUTE Replaces occurrences of characters in a string with a new set you specify.
T Checks if the value provided is a text or not.
TEXT Changes the format of numbers and dates.
TEXTJOIN Joins string items with a separator.
TRIM Removes leading, trailing, and extra spaces from a string.
UPPER Makes a string uppercase.

See also:

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

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.

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)
  • =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 date.

Syntax:

TEXT(value,format)

Here are some popular examples:

  • =TEXT(12345.678,"$#,###.##") — Currency with a thousands separator and 2 decimals, like $12,345.68.
    Note: Jotform Tables rounds the value to the number of decimal places specified in format.
  • =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.
Was this guide helpful?
Contact Support:

Our customer support team is available 24/7 and our average response time is between one to two hours.
Our team can be contacted via:

Support Forum: https://www.jotform.com/answers/

Contact Jotform Support: https://www.jotform.com/contact/

Send Comment:

Jotform Avatar
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Comments:

  • Ajit Shah