使用范围
函数列表
| 类型 | 函数 | 用途 | 举例 |
|---|---|---|---|
| 字符判断 | REGEXP | 判断字符串和正则表达式是否匹配 | REGEXP("Aaaaabbbbc","a*b*c",3)等于true REGEXP("Aaaaabbbbc","a*b*c",1)等于false |
| ENDWITH | 判断字符串结束情况 | ENDWITH("FineBI","BI")等于1 ENDWITH("FineBI","Fine")等于0 ENDWITH("FineBI","bi")等于0 | |
| STARTWITH | 判断字符串 str1 是否以 str2 开始 | STARTWITH("FineBI","Fine")等于1 STARTWITH("FineBI","BI")等于0 STARTWITH("FineBI","fine")等于0 | |
| EXACT | 检测两组文本是否相同 | EXACT("Spreadsheet","Spreadsheet")等于1 EXACT("Spreadsheet","S preadsheet")等于0 EXACT("Spreadsheet","spreadsheet")等于0 | |
| 字符串清洗 | FORMAT | 格式转换 | FORMAT(1234.5,"#,##0.00")=>1234.50 FORMAT(1234.5,"#,##0")=>1234 |
| TRIM | 清除首尾空格 | TRIM(" Monthly Report")等于Monthly Report | |
| LEFT | 截取前几个字符串 | LEFT("Finesoftware",8)等于"Finesoft" LEFT("Finesoftware")等于"F" | |
| SUBSTITUTE | 替换指定字符 | SUBSTITUTE("database","base","model")等于“datamodel” SUBSTITUTE("July28,2000","2","1",1)等于“July18,2000” | |
| MID | 求指定位置字符串 | MID("Finemoresoftware",9,8)返回“software”MID("Finemoresoftware",30,5)返回“”（空文本） | |
| SPLIT | 分割字符串 | SPLIT("hello,world,yes",",")=[hello,world,yes] SPLIT("this is very good"," ") = [this,is,very,good] SPLIT("thisisverygood","")=[t,h,i,s,i,s,v,e,r,y,g,o,o,d] | |
| UPPER | 转为大写字符 | UPPER("notes")等于“NOTES” | |
| RIGHT | 截取后几个字符串 | RIGHT("Itisinteresting",6)等于“esting” RIGHT("ShareHolder")等于“r” RIGHT("Hugesale",4)等于“sale” | |
| PROPER | 替换文本大小写 | PROPER("100 percent")等于“100 Percent” PROPER("SpreaDSheEt")等于“Spreadsheet” | |
| REPLACE | 替换指定位置字符 | REPLACE("0123456789",5,4,"*")等于“0123*89” REPLACE("1980",3,2,"99")等于“1999” | |
| CONCATENATE | 合并字段 | CONCATENATE("Average","Price")等于“AveragePrice” CONCATENATE("1","2")等于12。 | |
| LOWER | 大写转小写 | LOWER("A.M.10:30")等于“a.m.10:30” LOWER("China")等于“china” | |
| 类型转换 | TODOUBLE | 将文本转换成 Double 对象 | TODOUBLE("123.21")等于123.21 |
| TOINTEGER | 将文本转换成Integer对象 | TOINTEGER("123")等于123 | |
| NUMTO | 求字段的中文表示 | NUMTO(2345,true)等于二三四五 NUMTO(2345,false)等于二千三百四十五 NUMTO(2345)等于二千三百四十五 | |
| 字符信息 | INDEXOF | 求指定位置的字符 | INDEXOF("FineBI",0)等于'F' INDEXOF("FineBI",2)等于'n' INDEXOF("FineBI",5)等于'I' |
| CHAR | 求指定数值的字符 | CHAR(88)等于“X” CHAR(45)等于“-” | |
| CODE | 求数值代码 | CODE("S")等于83 CODE("Spreadsheet")等于83 | |
| FIND | 求字符串指定字符的位置 | FIND("I","Information")等于1 FIND("i","Information")等于9 FIND("o","Information",2)等于4 | |
| LEN | 求文本串中的字符数长度 | LEN("Evermoresoftware")等于16 LEN("Evermore software")等于17 LEN(["a","b"])等于2 | |
| REPEAT | 根据指定的次数重复显示文本 | REPEAT("$",4)等于“$$$$” REPEAT("你好",3)等于“你好你好你好” |
