Automatic identification of English and Chinese Excel data to be skillfully separated by three tricks

One day my friend asked me a question: how do you separate the English and Chinese in column B from the worksheets shown in Figure 1?

Figure 1

I provided him with the following three methods for his choice of use. The end of the article provides an. xls file for everyone to download for reference.

Method one, direct disaggregation method

1, start Excel2003 (other version please follow the operation), open the corresponding worksheet, in column B, insert a blank column to save the results of the columns.

2. Select column B, perform the data → columns command, open the Text Breakdown Wizard-3 Steps 1 dialog box (Figure 2), and select the Separator symbol-...... option (usually the default option).

Figure 2

3. Press "Next" button to enter the "Text Breakdown Wizard-3 Steps 2" dialog box (Figure 3),

Figure 3

Select the other option and enter the interval "-" Sign in the next box (because there is an interval "-" number in the B-column data, see Figure 1), and click the Finish button to complete the breakdown (see Figure 4).

Figure 4

Note: You can see the results of the columns in the Data Preview window in Figure 3.

Method Two, find the extraction method

1. Insert two blank columns to the right of column B to hold the proposed characters.

2, select C2 cell, input formula: =left (B2,find ("-", B2)-1).

Note: The meaning of the above formula is: Use the "find" function to look up the position of the interval "-" number in the string, and then use the "left" function to extract the string from the left-hand side of the string (that is, the English string, "1", to remove the interval "-" number).

3, select D2 cell, enter formula: =right (B2,len (B2)-find ("-", B2)).

Note: You can also enter formulas in D2: =replace (B14,1,find ("-", B14), ""). The meaning of this formula is to replace the English characters in the original string with the null character ("").

4. Select both C2 and D2 cells, and then move the mouse to the lower-right corner of the D2 cell. As a "fine cross" (usually called a "fill handle"), hold down the left mouse button and drag the formula above to the columns in column C and D, and complete the columns of the characters in the other cells of column B (as shown in Figure 4 )。