String

Read(348) Label: string,

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.

Generate a fixed-length string

 

A

 

1

=fill(" ",10)

"  "

2

=len(A1)

10

3

=fill("ab",10)

abababababababababab

 

Search for and replace a substring

 

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

 

Get part of a string

 

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

 

Concatenation of strings

 

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.

 

Upper- and lower-case letters identification and conversion

 

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

 

Remove blank spaces on both sides of a string

 

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

 

Format string matching

 

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

 

Match format strings with ' * '

 

A

 

1

=like("ab*123", "ab\*1?3")

Make "*" escaped with "\" to match the character itself.

2

=like("a*bcefg","a\*bc*")

true

 

Get Unicode and return characters by Unicode

 

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"

 

Split a string into a sequence

 

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

 

Concatenate members of a sequence into a string

 

 

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

 

Delete certain characters from a string

 

A

 

1

abcda123efag

 

2

=replace(A1,"123","")

Remove "123"

3

=replace(A1,"a","")

remove "a"

Check if a string consists of alphabetic and numeric characters

 

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

 

Compute an expression stored in a string

 

A

 

1

=eval("1+5")

Compute 1 + 5

2

=eval($[A1+2])

Compute A1 + 2

 

Use parameters to compute a string expression

 

A

 

1

=eval("?+5",3)

Equivalent to "3 + 5"

2

=eval("(?1+1)/?2",3,4)

Equivalent to "(3 + 1)/4"

 

Adjust string expressions during editing

 

A

 

1

=$[B1+4]

Strings written in the format of $[] will adjust themselves during editing

 

Check if it is string

 

A

 

1

=ifstring("abc")

true

 

Get MD5 signature string

 

A

 

1

=md5("abced")

EBB080AFAAC3A990AD3F1D0F21742FAC

 

Generate a random string of specified length

 

A

 

1

=rands("abc",5)

baaca

 

Match string with regular expression

 

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

 

Get substring from source string

 

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

 

Split away words/numbers from 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

 

Parse a string as string

 

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