The use of integrated Application Len (), LenB (), left (), find () function to filter Chinese characters in Excel

function function and structure introduction:

1.LEN returns the number of characters in a text string.
Len (text) len (string | varname)
The syntax for the Len function has the following parts:
String any valid string expression. If string contains null, NULL is returned.
Varname any valid variable name. If varname contains null, NULL is returned. If VarName is Variant,len, it is treated as a String and always returns the number of characters it contains.
2.lenb returns the number of bytes in a text string

Syntax format for 3.find functions

=find (Find_text,within_text,start_num)

Find (the text you want to find, the cell where the text resides, find [optional, omit default to 1, and find from the first]).

4.LEFT (text,num_chars) Text is a literal string that contains the characters you want to extract. num_chars Specifies the number of characters to be extracted by left. num_chars must be greater than or equal to 0. If Num_chars is greater than the text length, left returns all text. If Num_chars is omitted, it is assumed to be 1.

As shown in Excel, to remove all non-hardcode in the text column, the so-called hardcode is directly in the code, but "quotation marks are comments in the ABAP code, so the comments contain no hardcode in Chinese. All we have to do is sift through the pure hardcore and distribute it to the relevant developers to modify it.

In order to implement the above functions, a combination of several functions in Excel is used as follows:

=if (LEN (Left (A2,find ("" "", A2)-1) =lenb (left (A2,find ("" "", A2)-1)), 0, 1)

Explanation of the formula:

1, first use the Find function to find the "position in the string, if there is more than one string," The Find function returns the first "where it appears, that is, find (" "" ", A2), if there is no", then there will be #value

2, the left function is used to intercept the string, that is Ieft (A2,find ("" ", A2)-1)

3, using LEN,LENB to determine the number of characters of the second intercept string and the number of bytes, with the IF function to determine, if equal to the "left is all non-Chinese characters, with 0 marks, indicating that there is no hardcode;" If not equal to the left there are Chinese characters, with 1 marks, Description exists hardcore. That is, =if (LEN (Left (A2,find ("" "" ", A2)-1) =lenb (left (A2,find (" "" "", A2)-1)), 0, 1)

At this point, we will get three different values in the Type column, namely 0,1, #value. Where type 0 is non-hardcore, we should delete, for 1 and #value for hardcore, we should take out to send the relevant development to modify.

The use of integrated Application Len (), LenB (), left (), find () function to filter Chinese characters in Excel