Overview of Text Functions

Editing Data

Adding a column and filtering

Component

Adding a calculation field

Character judgment

REGEXP

Determines whether a string matches a regular expression.

REGEXP("Aaaaabbbbc","a*b*c",3) returns true.

REGEXP("Aaaaabbbbc","a*b*c",1) returns false.

ENDWITH

Determines the end situation of the string.

ENDWITH("FineBI","BI") returns 1.

ENDWITH("FineBI","Fine") returns 0.

ENDWITH("FineBI","bi") returns 0.

STARTWITH

Determines whether String 1 starts with String 2.

STARTWITH("FineBI","Fine") returns 1.

STARTWITH("FineBI","BI") returns 0.

STARTWITH("FineBI","fine") returns 0.

EXACT

Determines whether two text strings are identical.

EXACT("Spreadsheet","Spreadsheet") returns 1.

EXACT("Spreadsheet","S   preadsheet") returns 0.

EXACT("Spreadsheet","spreadsheet") returns 0.

String cleaning

FORMAT

Converts the format to the specified format.

FORMAT(1234.5,"#,##0.00") = >1234.50.

FORMAT(1234.5,"#,##0") = >1234.

TRIM

Clears all spaces at the beginning and end of the text.

TRIM(" Monthly Report") returns Monthly Report.

LEFT

Returns the first character or characters in a text string, based on the number of characters you specify.

LEFT("Finesoftware",8) returns Finesoft.

LEFT("Finesoftware") returns F.

SUBSTITUTE

Substitutes new texts for old texts in a text string.

SUBSTITUTE("database","base","model") returns datamodel.

SUBSTITUTE("July28,2000","2","1",1) returns July18,2000.

MID

Returns a certain number of characters from the specified position in the specified text string.

MID("Finemoresoftware",9,8) returns software. MID("Finemoresoftware",30,5) returns " " (empty text).

SPLIT

Splits the string.

SPLIT("hello,world,yes",",") returns [hello,world,yes].

SPLIT("this   is very good"," ") returns [this,is,very,good].

SPLIT("thisisverygood","") returns [t,h,i,s,i,s,v,e,r,y,g,o,o,d].

UPPER

Converts the text to uppercase.

UPPER("notes") returns NOTES.

RIGHT

Returns the last character or characters in the text string from the right based on the specified number of characters.

RIGHT("Itisinteresting",6) returns esting.

RIGHT("ShareHolder") returns r.

RIGHT("Hugesale",4) returns sale.

PROPER

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

PROPER("100 percent") returns 100 Percent.

PROPER("SpreaDSheEt") returns Spreadsheet.

REPLACE

Replaces the content in the original text with other text, based on the specified string.

REPLACE("0123456789",5,4,"*") returns 0123*89.

REPLACE("1980",3,2,"99") returns 1999.

CONCATENATE

Joins several text strings into one text string.

CONCATENATE("Average","Price") returns   AveragePrice.

CONCATENATE("1","2") returns 12.

LOWER

Converts all uppercase letters to lowercase letters.

LOWER("A.M.10:30") returns a.m.10:30.

LOWER("China") returns china.

Type conversion

TODOUBLE

Converts a text to a Double object.

TODOUBLE("123.21") returns 123.21, which is a new Double object.

TOINTEGER

Converts a text to an Integer object.

TOINTEGER("123") returns 123, which is a new Integer object.

NUMTO

Returns the number in Chinese.

NUMTO(2345,true) returns 二三四五.

NUMTO(2345,false)returns 二千三百四十五.

NUMTO(2345) returns 二千三百四十五.

Character information

INDEXOF

Returns the char at the index of the specified string.

INDEXOF("FineBI",0) returns F.

INDEXOF("FineBI",2) returns n.

INDEXOF("FineBI",5) returns I.

CHAR

Returns the character  specified by a number.

CHAR(88) returns X.

CHAR(45) returns -.

CODE

Calculates the numeric code of the first character in the text string. The returned code corresponds to the character set used by the computer.

CODE("S") returns 83.

CODE("Spreadsheet") returns 83.

FIND

Returns the index of the first specified sub-character string (specified by find_text) starting from the specified index (specified by start_num) in a character string (specified by within_text).

FIND("I","Information") returns 1.

FIND("i","Information") returns 9.

FIND("o","Information",2) returns 4.

LEN

Returns the number of characters of a text string.

LEN("Evermoresoftware") returns 16.

LEN("Evermore software") returns 17.

LEN(["a","b"]) returns 2.

REPEAT

Repeats a text a specified number of times.

REPEAT("$",4) returns $$$$.

REPEAT("你好",3) returns 你好你好你好.