CHAR
Description
CHAR(number)
Input | An integer between 1 and 65535 (including 1 and 65535). Type: integer. |
Output | A character whose code in the ASCII table is the input number. Type: string |
Examples
CHAR(41) >> ）
CHAR(58) >> :
CHAR(88) >> X
Reference: https://en.wikipedia.org/wiki/ASCII
CONCATENATE
Description
CONCATENATE(value1, value2,...)
Input | value1: the first piece of text to be contatenated. value2: the second piece of text to be contatenated.
Type: number, string or a cell containing text. |
|---|---|
Output | A contatenated text.
Type: string. |
Examples
CONCATENATE(1, "2") >> 12
If input text "3" in D5:
CONCATENATE(1.1, "2", D5) >> 1.123
ENDWITH
Description
ENDWITH(text, pattern)
Input | text: a main string. pattern: a pattern string.
Type: string or a cell containing text. |
|---|---|
Output | Whether text ends with pattern.
Type: boolean. |
Examples
ENDWITH("FineReport","Report") >> true
ENDWITH("FineReport","report") >> false
ENDWITH("FineReport","Repo") >>false
If input text "FineRport" in B11 and "Report" in C11:
ENDWITH(B11, C11) >> true
EXACT
Description
EXACT(text1, text2)
Input | text1: a string to be compared. text2: another string to be compared.
Type: string or a cell containing text. |
|---|---|
Output | Whether text1 and text2 are the same.
Type: boolean. |
Examples
EXACT("FineReport", "FineReport") >> true
EXACT("FineReport", "Fine Report") >> false
EXACT("FineReport", "finereport") >>false
If input text "FineRport" in B17 and C17:
EXACT(B17, C17) >> true
FORMAT
Description
FORMAT(value, format)
Input | value: a value to be formatted. Type: number, date or time. format: the reference format. |
|---|---|
Output | A formatted value.
Type: the same as the input. |
Examples
If the value is number:
FORMAT(1234.5, "#,##0.00") >> 1,234.50
FORMAT(1234.5, "#,##0") >> 1,234
FORMAT(1234.5, "$#,##0.00") >> $1,234.50
FORMAT(1.5, "0%") >> 150%
FORMAT(1.5, "0.000%") >> 150.000%
FORMAT(6789, "##0.0E0") >> 6.789E3
FORMAT(6789, "0.00E00") >> 6.79E03
If the value is date or time:
FORMAT(date(2007,1,13), "MM/dd/yyyy") >> 01/13/2007
FORMAT(date(2007,1,13), "M-d-yy") >> 1-13-07
FORMAT(date(2007,1,1), "EEEEE, MMMMM dd, yyyy") >> Monday, January 01, 2007
FORMAT(time(16,23,56), "h:mm:ss a") >> 4:23:56 PM
INDEXOF
Description
INDEXOF(value, index)
Input | value: a value to be searched. Type: string, array or a cell containing text, array or data column. format: the reference format. Type: integer. |
|---|---|
Output | The item whose position in value equals to index. The index of a string starts from 0 while the index of array and and data column starts from 1.
Type: string, array or data columb. |
Examples
INDEXOF("FineReport", 0) >> F
INDEXOF(ARRAY("Fine","Report"), 1) >> Fine
If cell D29 contains a data column with values [1,2,3,4,5]:
INDEXOF(D29, 1) >> 1
If cell B32 contains text "FineReport" and cell D32 contains ARRAY("Fine", "Report"):
INDEXOF(B32, 0) >> F
INDEXOF(D32, 1) >> Fine
LEFT
Description
LEFT(text, slice_length)
Input | text: a text to be searched. Type: string or a cell containing text. slice_length: the length of the slice. Type: integer. |
|---|---|
Output | The result of slicing text from the very left. If slice_length is longer than the length of text, return the whole text. If slice_length is not defined, return the first character of text.
Type: string. |
Examples
LEFT("FineReport", 2) >> Fi
LEFT("FineReport") >> F
LEFT("FineReport", 50) >> FineReport
If cell B38 contains text "FineReport":
LEFT(B38) >> Fi
LEN
Description
LEN(value)
Input | value: a text, array or data column to be examined. Type: string , array or a cell containing text, array or data column. |
|---|---|
Output | The length of the text, array or data column. For strings, space is counted into the length.
Type: Integer. |
Examples
LEN("FineReport") >> 10
LEN(" ") >> 1
LEN(ARRAY("Fine", "Report")) >> 2
If cell B47 contains a data column with values [1,2,3,4,5]:
LEN(B47) >> 5
If cell B44 contains text "FineReport" and cell D44 contains ARRAY("Fine", "Report"):
LEN(B44) >> 10
LEN(D44) >> 2
REPLACE
Description 1 (3 parameters)
REPLACE(original_text, olc_char, new_char)
Input | original_text: the original text whose characters will be replaced. Type: string or cells that contain text.
olc_char: characters in the original_text that will be replaced. Type: string.
new_char: the new characters that to take the place of old_char. Type: string. |
|---|---|
Output | Output a new text after replacing some characters. Type: string |
Examples
REPLACE("abcd", "a", "re") >> rebcd
All old_chars will be replaced
REPLACE("abcda", "a", "re") >> rebcdre
Description 2 (4 parameters)
REPLACE(original_text, start_of_char, number_of_char,new_char)
Input | original_text: the original text whose characters will be replaced. Type: string or cells that contain text.
start_of_char: the starting index of the characters that will be replaced. For string, the first character has an index of 0. Type: integer.
number_of_char: the number of characters to be replaced. Type: integer.
new_char: the new characters that to take the place of char_to_replace. Type: string. |
|---|---|
Output | Output a new text after replacing some characters. Type: string. |
Examples
REPLACE("0123456789",5,4,"*") >> 0123*89
REPEAT
Description
REPEAT(pattern, repeat_times)
Input | pattern: the text to be repeated. Type: string or cells that contain text.
repeat_times: the number of times that the text will be repeated. Type: integer. |
|---|---|
Output | Output a text as a result of repeating pattern. Type: string. |
Examples
REPEAT("$",4) >> $$$$
If input "hello" in C53:
REPEAT(C53,3) >> hellohellohello
RIGHT
Description
RIGHT(searched_text, num_of_char)
Input | searched_text: the text to be searched. Type: string or cells that contain text.
num_of_char: the number of characters to be extracted. The default is 1. Type: integer |
|---|---|
Output | Return the number of characters extracted from searched_text from right to left. |
Examples
RIGHT("FineReport",2) >> rt
When num_of_char is not specified, use the default value:
RIGHT("FineReport") >> t
When num_of_char exceeds the length of searched_text:
RIGHT("FineReport",20) >> FineReport
SPLIT
Description
SPLIT(text_to_split, delimeter)
Input | text_to_split: the text to be split into several parts. Type: string.
delimeter: a text by which the text_to_split will be split. Type: string. |
|---|---|
Output | Output an array whose elements are the split result. Type: array. |
Examples
SPLIT("Fine,Report",",") >> "Fine", "Report"
SPLIT("Fine Report"," ") >> "Fine", "Report"
SUBSTITUTE
Description
SUBSTITUTE(original_text, old_char, new_char, char_index)
Input | original_text: the original text whose characters will be replaced. Type: string or cells that contain text.
old_char: characters in the original_text that will be replaced. Type: string.
new_char: the new characters that to take the place of old_char. Type: string.
char_index: when old_char appears more than once in original_text, this index determines which one will be substituted. If not specified, each old_char will be substituted. |
|---|---|
Output | Output a new text after substituting some characters. Type: string |
Examples
SUBSTITUTE("July 28, 2000","2","1",1) >> July 18, 2000
SUBSTITUTE("July 28, 2000","2","1") >> July 18, 1000
TRIM
Description
TRIM(original_text)
Input | original_text: the text whose space at the begin and end will be removed. Type: string or cells that contain text. |
|---|---|
Output | Output a new text after removing the space at the begin and end for original_text. Type: text. |
Examples
TRIM(" Fine Report ") >> "Fine Report"
