String

Read(217) 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, and Adjuste string expression during editing.

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

="3"+2

The result is 5 because the string is taken as number when computed with numbers.

3

="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

 

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 parameter s to comput e 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 e xpression s during editing

 

A

 

1

=$[B1+4]

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