Common string processing functions

LEN(args): Returns the number of characters in the text string or the length of the array.

args: The text string or array used to calculate the length.

Note: When the parameter args is a text string, spaces are also counted as characters. When the parameter args is an array, the length of the array is directly returned.

Example:

LEN("Evermoresoftware") is equal to 17.

LEN(" ") is equal to 1.

LEN(['a','b']) is equal to 2.

MID(text,start_num,num_chars): Returns a string of a certain length starting at the specified position of the string.

text: The text string containing the characters to be extracted.

start_num: The starting position of the character to be extracted in the text. The start_num of the first character in the text is 1.

num_chars: Returns the length of characters.

Note 1: If start_num is greater than the length of the text, the MID function returns "" (empty text).

Note 2: If start_num is less than the length of the text, and start_num plus num_chars is greater than the length of the text, the MID function will start all the characters from the start character specified by start_num to the end of the text.

Note 3: If start_num is less than 1, the MID function returns the error message *VALUE!.

Note 4: If num_chars is a negative number, the MID function returns the error message *VALUE!.

Example:

MID("Finemoresoftware",9,8) returns "software".

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

MID("Finemoresoftware",0,8) returns *VALUE!.

MID("Finemoresoftware",5,-1) returns *VALUE!.

LEFT(text,num_chars): Return the first or first few characters in the text string from the left according to the specified number of characters.

text: The text string or cell reference that contains the characters to be selected.

num_chars: Specify the length of the returned string.

Note: The value of Num_chars must be equal to or greater than 0. If num_chars is greater than the length of the entire text, the LEFT function will return all the text. If num_chars is omitted, the default value is 1.

Example:

LEFT("Finesoftware",8) is equal to "Finesoft".

LEFT ("Finesoftware") is equal to "F".

If cell A3 contains "China", LEFT(A3,2) is equal to "Ch".

RIGHT(text,num_chars): Return the last character or characters in the text string from the right according to the specified number of characters.

text: The text string or cell reference that contains the characters to be extracted.

num_chars: Specifies the number of characters extracted by the RIGHT function from the text string.

Note: Num_chars cannot be less than 0. If num_chars is greater than the length of the text string, the RIGHT function will return the entire text. If num_chars is not specified, the default value is 1.

Example:

RIGHT("Itisinteresting",6) is equal to "esting".

RIGHT("ShareHolder") is equal to "r".

RIGHT("Hugesale",4) is equal to "sale".

1) REPLACE(text, texttoreplace, replacetext): According to the specified string, replace the content in the original text with other text.

text: The text or cell reference that needs to be replaced with some characters.

texttoreplace: The specified string.

replacetext: The text that needs to replace part of the old text.

Example:

REPLACE("abcd","a","re") is equal to "rebcd".

REPLACE("a**d","**d","rose") is equal to "arose".

2) REPLACE (old_text, start_num, num_chars, new_text): According to the specified number of characters, replace part of a text string with another text string.

old_text: The text or cell reference that needs to be replaced with some characters.

start_num: Need to use new_text to replace the starting position of the characters in old_text.

num_chars: Need to use new_text to replace the number of characters in old_text.

new_text: The text that needs to replace part of the old text.

Example:

REPLACE("0123456789",5,4,"*") is equal to "0123*89".

REPLACE("1980",3,2,"99") is equal to "1999".

FIND(find_text,within_text,start_num): Starting from the specified index (start_num), return the index of the first occurrence of the specified substring (find_text) in this string (within_text).

find_text: The text to be found or the cell reference containing the text.

within_text: contains the text or cell reference that needs to find the text.

start_num: Specify the index position of the search character. The index in within_text starts from 1. If start_num is omitted, the value is assumed to be 1.

Note 1: If find_text is not in within_text, the FIND function returns a value of 0.

Note 2: If start_num is not greater than 0, the FIND function returns the error message *VALUE!.

Note 3: If start_num is greater than the length of within_text, the FIND function returns a value of 0.

Note 4: If find_text is blank text, the FIND function will match the first character in the search string (ie the character numbered start_num or 1).

Example:

FIND("I","Information") is equal to 1.

FIND("i","Information") is equal to 9.

FIND("o","Information",2) is equal to 4.

FIND("o","Information",12) is equal to 0.

FIND("o","Information",-1) is equal to *VALUE!.