Design Guide
Design Guide
Formulas for obtaining field value strings or checking field content, such as getting string characters, changing case, checking for null values, etc.
Below is the list of the supported formulas. Please note that the following formulas are case-sensitive.
| Formula | Description |
|---|---|
| LEFT(value,length) | Returns the first character or characters (from the left side) of a text string, based on the number of characters(length) you specify.
Example: If the length is 3, it will return the left most 3 characters. For details, click here. |
| RIGHT(value,length) | Returns the last character or characters (from the right side) of a text string, based on the number of characters(length) you specify.
Example: If the length is 3, it will return the right most 3 characters. For details, click here. |
| MID(value,start,[length]) | Extracts a given number of characters from the middle of a supplied text string. For the starting character, the first character on the referenced field will be specified as 0. For example, if the value on field A1 is ABCD, setting the formula as MID(A1,1,2) on another field will return BC. For details, click here. |
| FIND(find_text,within_text,[start_num]) | Locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. For details, click here. |
| LEN(value) | Returns the number of characters in a text string. For details, click here. |
| UPPER(value)/TOUPPERCASE(value) | Converts all lowercase letters in a text string to uppercase without changing the original string. |
| LOWER(value)/TOLOWERCASE(value) | Converts all uppercase letters in a text string to lowercase without changing the original string. |
| PROPER(value) | Capitalizes the first letter in a text string and any other letters in a text that follow any character other than a letter. Converts all other letters to lowercase letters. |
| SUBSTITUTE(text,old_text,new_text,[instance_num]) | Substitutes new_text for old_text when you want to replace specific text in a text string. |
| TEXT() | Formats a number or date value into a specified format. For details, click here. |
| REPT(value,number_times) | Returns the repeated value a given number of times. For details, click here. |
| SPELLNUMBER(number, [lang]) | You will see numbers that are written in words in some formal documents. For example, use "one hundred" instead of "100". You can use SPELLNUMBER formula if you need to see numbers in words in your sheets. For details, click here. |
| TRIM() | Remove fullwidth and halfwidth spaces at the beginning and the end of a field value. And if there are multiple full-width and half-width spaces between texts, it will only keep the first space. Example: TRIM(" a c ") will get "a c". |
| CHAR(value) | Returns a character when given a valid character code. For example, CHAR(10) returns a line break, and CHAR(32) returns a space. |
| ISBLANK() | Checks whether the referenced field is empty. You can directly reference specified fields or use them in conditional formulas.
For example, ISBLANK(A2) or IF(ISBLANK(A2), 'Y', 'N'). |
A string formula is straightforward: if the value in C1 is "Michael", and C2 is "Scott", then "C1+C2" will be "MichaelScott". If you want to add a space between two strings, you can use "C1+' '+C2", resulting in "Michael Scott".
Note: If you want to represent "\" in a formula, it should be written as "\\".
You can combine functions such as LEFT(), MID(), RIGHT(), FIND(), and LEN() to flexibly extract specific text from a text field.
| Formula | Description |
|---|---|
| LEFT(value,length) | Returns the first character or characters (from the left side) of a text string, based on the number of characters(length) you specify.
Example: If the length is 3, it will return the left most 3 characters. |
| RIGHT(value,length) | Returns the last character or characters (from the right side) of a text string, based on the number of characters(length) you specify.
Example: If the length is 3, it will return the right most 3 characters. |
| MID(value,start,[length]) | Extracts a given number of characters from the middle of a supplied text string. For the starting character, the first character on the referenced field will be specified as 0. For example, if the value on field A1 is ABCD, setting the formula as MID(A1,1,2) on another field will return BC. |
| FIND(find_text,within_text,[start_num]) | Locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. |
| LEN(value) | Returns the number of characters in a text string. |
Example 1: Extracting Text Before and After a Specified Character
For example, the value in the "Full Name" field is "John Smith".
If you want to display the first name and last name in separate fields, you can use the following formulas:
(1) Extract the first name
Enter the formula below in the "First Name" field: LEFT(A1, FIND(" ", A1, 1))
This formula uses the FIND function to locate the space (" ") in the full name, then the LEFT function extracts the text to the left of that space, resulting in "John".
(2) Extract the last name
Enter the formula below in the "Last Name" field: RIGHT(A1, LEN(A1) - FIND(" ", A1, 1))
This formula uses the LEN function to count the total number of characters, subtracts the position of the space, and then uses the RIGHT function to extract the text to the right of that space, resulting in "Smith".
This way, the "Full Name" field can be automatically split into "First Name" and "Last Name".
Example 2: Extract a substring from the middle of text
If the value in the "Product No." field is "PRD-2023-001", where the middle four digits indicate the year, enter MID(A1, 4, 4) in the "Year" field.
This extracts four characters starting from the fifth position (index starts at 0), resulting in 2023.
Use this function to apply a specific format to a field.
| Formula | Syntax |
|---|---|
| TEXT | TEXT(value, format_text) |
Arguments:
value (required): The number or date to format; can reference a field.
format_text (required): The format to apply.
Numeric Fields
For the format 12,345.67, you can use TEXT(A1, '#,###.##').
This formula can also be applied to other Numeric field formats.
Date Fields
To display the full weekday name (e.g., "Friday"), use TEXT(A1,'EE'). To display the abbreviated form (e.g., "Fri"), use TEXT(A1,'E').
For other formatting options, please refer to this document.
Repeats specified text or numbers a set number of times automatically. Useful for formatting, alignment, or displaying ratings with symbols.
| Formula | Syntax |
|---|---|
| REPT | REPT(value,number_times) |
Arguments:
value (required): The text or number to repeat; can reference a field or be entered directly.
number_times (required): The number of repetitions; must be a positive integer.
Example: Display satisfaction levels with repeated symbols
In the "Star Rating" field, enter REPT("*", "Satisfaction Rating") to represent the score with stars.
The system automatically repeats the symbol to visually display the rating.
Thank you for your valuable feedback!
