Jotform for Beginners
Get more done with powerful, easy-to-use online forms. Learn how in this helpful, free guide from Jotform.
Download the Book
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:
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.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.Returns the length of text
.
Syntax:
LEN(text)
Examples:
=LEN("Sunday")
will return 6.=LEN("Hello World")
will return 11.Converts text
to lowercase.
Syntax:
LOWER(text)
Examples:
=LOWER("SUNDAY")
will return sunday. =LOWER("Hello World")
will return hello world.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.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!.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.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.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.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.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
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.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.Converts text
to uppercase.
Syntax:
UPPER(text)
Examples:
=UPPER("Sunday")
will return SUNDAY.=UPPER("Hello World")
will return HELLO WORLD.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:
1 Comments:
In Product order form, response data for Product field is copied to single column in excel (or CSV) report. So multiple product details are copied to single cell in excel.
Is it possible to copy form data to respective product columns? This would help to create report of total of each product quantity required. Please share how this can be achieved.