Text Functions

A text function is a type of function in a formula that can operate on data of text type.

CONCATENATE()

Concatenates multiple text values into a single text value (the effect is equivalent to the operator "&").

CONCATENATE(text1, [text2, …])

Parameter Description

text: is the text value to concatenate.Enter at least one text value, text1, and you can add more than one textn....
Note ① : If you want to concatenate double quotes, you need to use the backslash () as the escape character.

Example

// Text concatenation
Formula: CONCATENATE(" Class 8 ", "Little Hu ")
Result: 8 class lane ""

// Concatenate {fields} of text types with text
Formula: CONCATENATE({class}, "Hu ")
Result: text (example: "Class 8 Xiao Hu")

// Concatenate {fields} with text types
Formula: CONCATENATE({class},{name})
Result: text (example: "8 class lane")

Formula: CONCATENATE({class}, "- ", {name})
Result: text (example: "Class 8 -- Xiao Hu")

// Concatenate the text with {field} of the date type
Formula: CONCATENATE(" Project to be completed by: ", {deadline}, ")
Result: text (example: "Project to be completed by 2021/4/1")

// Concatenate text with {field} of numeric type
Formula: CONCATENATE(" Sales this month: ", {sales})
Result: text (example: "Sales this month: 100")

// Special case: concatenate double quotes
Formula: CONCATENATE(" \" ", "Xiao Hu "," \" ")
Result: "Xiao Hu"

FIND()

Finds the first place in the text where a particular item appears.

FIND(stringToFind, whereToSearch, [startFromPosition]);  

Parameter Description

stringToFind: is the specific content to find. whereToSearch: specifies which text to look for. startFromPosition: Optional, specifies where in the text to start the search (with a number representing the first character).
If the number 3 is returned, it indicates that the content first appeared in the third character of the text. If no matching text is found, the result will be 0. has a similar effect to SEARCH(), but when no match is found, SEARCH() returns null and FIND() returns 0.

Example

// If a match is found, return the location where the content first appeared.
Formula: FIND("API", "Intelligent multidimensional table with API support ");
Result: 3;

Formula: FIND(" Support ", "Support API smart multidimensional tables ");
Result: 1;

Formula: FIND(
  "API",
  "intelligent multidimensional tables that support apis.Support API, unlimited DIY"
);
Result: 3;

// No match found, return 0.
Formula: FIND(
  " Vig table ",
  "Intelligent multidimensional table with API support "
);
Result: 0;

// specifies that the search starts somewhere in the text.
Formula: FIND("API", "Intelligent multidimensional table with API support ", 4);
Result: 0;

Formula: FIND(
  "API",
  "intelligent multidimensional tables that support apis.Support API, unlimited DIY",
  4
);
Result: 16;

// Look up the contents from a column {field}.
Formula: FIND("API", { text }, 4);
Result: Number;

SEARCH()

Finds the first place in the text where a particular item appears.

SEARCH(stringToFind, whereToSearch, [startFromPosition]);    

Parameter Description

stringToFind: is the specific content to find. whereToSearch: specifies which text to look for. startFromPosition: Optional, specifies where in the text to start the search (with a number representing the first character).
If the number 3 is returned, it indicates that the content first appeared in the third character of the text. If no matching text is found, the result will be 0. Has a similar effect to FIND(), but when no match is found, SEARCH() returns null and FIND() returns 0.

Example

// If a match is found, return the location where the content first appeared.
Formula: SEARCH("API", "API-enabled smart multidimensional Table ")
Result: 3

Formula: SEARCH(" Support ", "Intelligent multidimensional tables with API support ")
Result: 1

Formula: SEARCH("API", "intelligent multidimensional table that supports API.Support API, unlimited DIY")
Result: 3

// No match found, return null.
Formula: SEARCH(" Vig table ", "Intelligent multidimensional Table with API support ")
Result: Null

// specifies that the search starts somewhere in the text.
Formula: SEARCH(" Vig table ", "Intelligent multidimensional Table with API support ")
Result: Null

Formula: SEARCH("API", "intelligent multidimensional table that supports API.Support API, unlimited DIY", 4)
Result: 16

// Look up the contents from a column {field}.
Formula: SEARCH("API", {article content}, 4)
Result: Number

MID()

Extract a fixed length piece of content from a specific location in the text.

MID(string, whereToStart, count);

Parameter Description

string: is a piece of text to extract a specific content. whereToSearch: specifies where to start extracting content, represented as a number.For example, the number 3 means to extract from the third character of the text. count: is the length of the extracted content, expressed as a number.For example, the number 2 means to extract two characters from the specified position.

Example

// Extract the contents to the specified location.
Formula: MID(" Intelligent multidimensional table with API support ", 3, 3)
Result: "API"

// The length of the extracted content exceeds the length of the text.
Formula: MID(" Intelligent multidimensional table with API support ", 3, 99)
Result: "API's Intelligent Multidimensional Table"

// No content was extracted at the specified location.
Formula: MID(" Intelligent multidimensional table with API support ", 99, 3)
Result: Null

// Extract the contents of a column {field} at the specified position.
Formula: MID({article content}, 3, 3)
Result: text

REPLACE()

Replaces a paragraph of content at a specific location in the text with new content.

REPLACE(string, start_character, number_of_characters, replacement); 

Parameter Description

string: is a piece of text that you want to replace with something specific. start_character: specifies where to start the replacement, represented as a number.For example, the number 3 indicates that the replacement starts at the third character of the text. number_of_characters: specifies the length of the replacement content.For example, the number 2 means to replace two characters from the specified position. replacement: is the new content that replaces the original content.
Note ① : If you want to replace all specific items in the text with new items, see 'SUBSTITUTE()'.

Example

// Replaces the contents of the specified position.
REPLACE(" Wiggle table ", 3, 1, "Planet ")
Result: "Planet Vige"

// The length of the replacement exceeds the length of the text itself.
REPLACE(" Wiggle Table ", 3, 99, "Planet ")
Result: "Planet Vige"

// The specified position is longer than the text itself.
REPLACE(" Wiggle Table ", 99, 1, "Planet ")
Result: "Planet of the dimension table"

// Replaces the contents of a column {field} at the specified position.
REPLACE({article content}, 3, 1, "planet ")
Result: text

SUBSTITUTE()

Replaces all text specific content with new content.

SUBSTITUTE(string, old_text, new_text, [index]); 

Parameter Description

string: is a piece of text that you want to replace with something specific. old_text: the original content to be replaced. new_text: new content that replaces the original content. index: specifies the index number. After the index number is specified, the system will replace only the content in a specific position.If this parameter is not filled, the system will replace all the matching content in the text.
Note ① : If you want to REPLACE something between the specified start and end positions, see 'REPLACE()'.

Example

// Replace all matches in the text.
SUBSTITUTE: " Xiao Hu, Xiao Zhang, Xiao Wang ", "xiao ", " Lao ";
Result: "Lao Hu, Lao Zhang, Lao Wang";

// No replacement was found
SUBSTITUTE: " Xiao Hu, Xiao Zhang, Xiao Wang ", "Lao ", " Da ";
Result: "Xiao Hu, Xiao Zhang, Xiao Wang";

// Replace all matches in a column {field}.
SUBSTITUTE({ article }, "table ", " planet ");
Result: text;

LEN()

Counts the character length of a piece of text.

LEN(string);

Parameter Description

string: the length of the text to be computed; Punctuation marks, Spaces, etc., also take up a character.

Example

// Statistics the length of text characters.
Formula: LEN(" Guess how long I am?" )
Result: 8

// Null values do not occupy characters.
Formula: LEN (" ")
Result: 0

// Space takes one character (there is a space between quotes).
Formula: LEN(" ")
Result: 1

// Count the length of characters in a cell for a column {field}.
Formula: LEN({article content})
Result: number

LEFT()

Extracts a specified number of characters from the beginning of the text.

LEFT(string, howMany);

Parameter Description

string: is the text of the character to be extracted. howMany: is the number of characters extracted.Use a number, such as "5", to extract five characters from right to left.

Example

// Extract the characters inside the text.
Formula: LEFT(" Support API, feel free to DIY", 5)
Result: "API supported"

// Space takes one character (there is a space between quotes).
Formula: LEFT(" Support API, feel free to DIY", 5)
Result: "Supports AP"

// Extract the characters in the cell of a column {field}.
Formula: LEFT({article content}, 99)
Result: text

RIGHT()

Extracts a specified number of characters from the end of the text.

RIGHT(string, howMany);  

Parameter Description

string: is the text of the character to be extracted. howMany: is the number of characters extracted.Represented by a number, such as "5," represents five characters from right to left.

Example

// Extract the characters inside the text.
Formula: RIGHT(" Support API, feel free to DIY", 5)
Result: "Feel free to DIY"

// Space takes one character (there is a space between quotes).
Formula: RIGHT(" Support API, feel free to DIY", 5)
Calculation result: "meaning DIY"

// Extract the characters in the cell of a column {field}.
Formula: RIGHT({text}, 99)
Result: text

LOWER()

Converts all uppercase letters to lowercase letters.

LOWER(string);

Parameter Description

string: is the text to be converted.

Example

// Repeat the text twice.
Formula: REPT(" ha ", 2)
Result: "Ha ha"

// Repeat the number twice, and the output is the text number
Formula: REPT(5, 2)
Result: "55"

// Repeat the text in the cell of a column {field} twice.
Formula: REPT({article content}, 2)
Result: text

UPPER()

Converts all lowercase letters to uppercase letters.

UPPER(string);

** Parameter Description **

string: is the text to be converted.

Example

// Converts lowercase letters in text to uppercase letters.
运算结果："HELLO!"
Formula: UPPER({article content})
Result: text

REPT()

Copies the text contents as many times as specified.

REPT(string, number);

Parameter Description

string: indicates the text to be copied. mumber: indicates the specified number of times of replication.Use a number, like "2," to repeat twice.

Example

// Repeat the text twice.
Formula: REPT(" ha ", 2)
Result: "Ha ha"

// Repeat the number twice, and the output is the text number
Formula: REPT(5, 2)
Result: "55"

// Repeat the text in the cell of a column {field} twice.
Formula: REPT({article content}, 2)
Result: text

T()

Determines if the content is a text value.

T(value);

Parameter Description

value: indicates whether the value is a text value.
If the input value is of text type, the original text is returned; if the input value is of non-text type, the null value is returned.

Example

// If the input value is text, the original text is returned.
Formula: T(" AITable ")
Result: "AITable"

// If the input value is a text number, return the original text.
Formula: T (" 2 ")
Result: "2"

// If the input value is a number, null is returned.
T (2) formula:
Result: Null

// Determines whether text is in the cells of a column {field}.
Formula: T({article content})
Result: text

TRIM

Clears the space at the beginning and end of the text.

TRIM(string);

Parameter Description

value: indicates the text to be processed.

Example

// The space on either side of the text will be cleared.
Formula: TRIM(" Spaces will be cleared! ");
Result: "The space on both sides will be cleared!";

// The space in the middle of the text is not cleared.
Formula: TRIM(" Middle space won't be cleared!");
Result: "The middle space will not be cleared!";

// Clear the space before and after the cell text in a column {field}.
Formula: LOWER({text})
Result: text

ENCODE_URL_COMPONENT()

Encodes the text into the format of a URL.

ENCODE_URL_COMPONENT(component_string);

** Parameter Description **

componentstring: specifies the text to be encoded.The following characters are not encoded: - . ~ For example, copying the output of the first example to the browser address bar is equivalent to searching for the URL for "Apple" in Baidu.

Example

// Encode "apple" in URL format.The URL equivalent of searching for "Apple" on Baidu.
Formula: "https://www.baidu.com/s?wd=" &ENCODE_URL_COMPONENT (" apple ")
Result: URL

// Encode the text content in the {search term} cell into URL format.Equivalent to the URL of search {search keyword} cell content in Baidu.
Formula: "https://www.baidu.com/s?wd=" & ENCODE_URL_COMPONENT ({search keyword})
Result: URL

Text Functions

CONCATENATE()​

FIND()​

SEARCH()​

MID()​

REPLACE()​

SUBSTITUTE()​

LEN()​

LEFT()​

RIGHT()​

LOWER()​

UPPER()​

REPT()​

T()​

TRIM​

ENCODE_URL_COMPONENT()​

CONCATENATE()

FIND()

SEARCH()

MID()

REPLACE()

SUBSTITUTE()

LEN()

LEFT()

RIGHT()

LOWER()

UPPER()

REPT()

T()

TRIM

ENCODE_URL_COMPONENT()