This chapter lists code examples about handling strings, including Generate a fixed-length string, Search for and replace a substring, Get part of a string, Concatenation of strings, Upper and lower-case letters identification and conversion, Remove blank spaces on both sides of a string, Format string matching, Match format strings with ' *', Get Unicode and return characters by Unicode, Split a string into a sequence, Concatenate members of a sequence into a string, Delete certain characters from a string, Check if a string consists of alphabetic and numeric characters, Compute an expression stored in a string, Use parameters to compute a string expression, Adjuste string expression during editing, Check if it is string, Get MD5 signature string, Generate random string of specified length, Match string by regular expression, Get substring from source string, Split away words/numbers from string and Parse a string as string.
|
|
A |
|
|
1 |
=fill(" ",10) |
" " |
|
2 |
=len(A1) |
10 |
|
3 |
=fill("ab",10) |
abababababababababab |
|
|
A |
|
|
1 |
=pos("abcdef","def") |
Determine the position of "def" in "abcdef" |
|
2 |
=pos("abcdefdef","def",5) |
Search from the fifth character |
|
3 |
=replace("abca","a","ABC") |
Replace "a" with "ABC" in "abca" |
|
4 |
=replace("abc'abc'","a","ABC") |
The substring in the single quotation marks will also be replaced |
|
5 |
=replace@q("abc'abc'","a","ABC") |
The substring in the single quotation marks will not be replaced |
|
|
A |
|
|
1 |
=mid("abcde",2,1) |
Get the second character |
|
2 |
=mid("abcde",3,2) |
Get 2 characters from the third position |
|
3 |
=mid("abcde",2) |
Get characters from the second position to the end |
|
4 |
=left("abcdefg",3) |
The leftmost three characters |
|
5 |
=right("abcde",2) |
The rightmost two characters |
|
|
A |
|
|
1 |
="ab"+"cd" |
abcd |
|
2 |
="ab"/"cd" |
abcd |
|
3 |
="3"+2 |
The result is 5 because the string is taken as number when computed with numbers. |
|
4 |
="ab"+1 |
The result is 1 because the string that can’t be converted to a number will be taken as 0. |
|
|
A |
|
|
1 |
=upper("abcdef") |
"ABCDEF" |
|
2 |
=upper("ABCdef") |
"ABCDEF" |
|
3 |
=lower("abcDEF") |
"abcdef" |
|
4 |
=isupper("ABC") |
true |
|
5 |
=islower("ABC") |
false |
|
6 |
=islower("aBc") |
false |
|
7 |
=isupper("Bc") |
false |
|
|
A |
|
|
1 |
=trim(" abc ") |
Remove spaces on both sides |
|
2 |
=trim@l(" abc ") |
Remove the spaces on the left |
|
3 |
=trim@r(" abc ") |
Remove the spaces on the right |
|
|
A |
|
|
1 |
=like("abc123","abc1?3") |
"?" is used to match a single character |
|
2 |
=like("abcefg","abc*") |
"*" is used to match 0 or multiple characters |
|
|
A |
|
|
1 |
=like("ab*123", "ab\*1?3") |
Make "*" escaped with "\" to match the character itself. |
|
2 |
=like("a*bcefg","a\*bc*") |
true |
|
|
A |
|
|
1 |
=asc("a") |
Get the unicode of character "a" |
|
2 |
=char(68) |
Get the character corresponding to code "68" |
|
3 |
=asc("USA") |
Get the Unicode corresponding to character "U" |
|
|
A |
|
|
1 |
="12345678" |
|
|
2 |
=len(A1) |
|
|
3 |
=A1.split() |
Split A1 into a sequence of characters |
|
4 |
="a,[b,c],d" |
|
|
5 |
=A4.split@cp() |
Return a sequence which consists of three members a ,[b,c], d where the member [b,c] is a sequence |
|
6 |
=A4.split@c() |
Return a sequence which consists of three members a ,[b,c], d where the member [b,c] is a string instead of a sequence |
|
7 |
=A4.split@cb() |
Return a sequence which consists of four members a ,[b,c], d. The quotation marks and brackets will not be matched. |
|
8 |
="a;[b;c];d".split@p(";") |
Use ";" as the delimiter |
|
9 |
="a b c" |
|
|
10 |
=A1.split@t() |
Return a sequence consisting of a, b, c and perform trim on both sides |
|
11 |
="a1b2c".split@r("(\\d)") |
Return a sequence consisting of a, b, c where the delimiter is a regular expression |
|
|
A |
|
|
1 |
=[1,"abc,def",[2,4],"{7,8}"] |
|
|
2 |
=A1.concat@cq() |
Use "," as the delimiter to concatenate members , in which strings will be quoted |
|
3 |
=A1.concat() |
String members don't need to be quoted and separated when being concatenated |
|
4 |
=A1.concat("&") |
Use "&" as the delimiter |
|
|
A |
|
|
1 |
abcda123efag |
|
|
2 |
=replace(A1,"123","") |
Remove "123" |
|
3 |
=replace(A1,"a","") |
remove "a" |
|
|
A |
|
|
1 |
2345$#dfAgsdf23* |
|
|
2 |
=len(A1) |
16 |
|
3 |
=A1.split() |
Split A1 into a sequence of characters |
|
4 |
=A3.count(!isdigit(~) && !isalpha(~))==0 |
Check if it is a alphanumeric string |
|
|
A |
|
|
1 |
=eval("1+5") |
Compute 1 + 5 |
|
2 |
=eval($[A1+2]) |
Compute A1 + 2 |
|
|
A |
|
|
1 |
=eval("?+5",3) |
Equivalent to "3 + 5" |
|
2 |
=eval("(?1+1)/?2",3,4) |
Equivalent to "(3 + 1)/4" |
|
|
A |
|
|
1 |
=$[B1+4] |
Strings written in the format of $[] will adjust themselves during editing |
|
|
A |
|
|
1 |
=ifstring("abc") |
true |
|
|
A |
|
|
1 |
=md5("abced") |
EBB080AFAAC3A990AD3F1D0F21742FAC |
|
|
A |
|
|
1 |
=rands("abc",5) |
baaca |
|
|
A |
|
|
1 |
4,233,a,test |
|
|
2 |
=A1.regex("(\\d),([0-9]*),([a-z]),([a-z]*)") |
Return a sequence of [4, 233, a, test] |
|
3 |
4,233,A,tEst |
|
|
4 |
=A3.regex@c("(\\d),([0-9]*),([a-z]),([a-z]*)") |
Return a sequence consisting of [4, 233, A, tEst]; case-insensitive |
|
5 |
小明,中国 |
|
|
6 |
=A5.regex@u("(\\u5c0f\\u660e),(\\u4e2d\\u56fd)") |
Return a sequence of [小明,中国], where the elements are Chinese chracters |
|
|
A |
|
|
1 |
=substr("abcdef","cd") |
ef, return string after the specified substring |
|
2 |
=substr@l("abcdef","cd") |
ab, return string before the specified substring |
|
3 |
=substr@q("ab\'cdef\'","cd") |
null, ignore quoted string |
|
|
A |
|
|
1 |
4℃,23,a,test?my_file 57b |
|
|
2 |
=A1.words() |
a,test,my,file,b; English words are split away from string |
|
3 |
=A1.words@d() |
4,23,57;numbers are split away from string |
|
4 |
=A1.words@a() |
4,23,a,test,my,file,b,57; both English words and numbers are split away from string |
|
5 |
=A1.words@i() |
a,test,my,file,b57; neiigboring English letters and numbers are treated as a whole |
|
6 |
=A1.words@w() |
4,℃, ,, 23, ,, a, ,, test, ?, my, _, file, , b,57; all characters are split away |
|
|
A |
|
|
1 |
="10:20:30" |
|
|
2 |
=parse(A1) |
10:20:30, time type |
|
3 |
="10℃" |
|
|
4 |
=parse@n(A3) |
10, Only parse the number in a number headed string |