It's that simple! Seconds mention Chinese and English text

In daily work, we often encounter various kinds of data in Chinese and English, and we need to extract the mixed Chinese and English separately for the text specification. The conventional method is to copy and paste the contents into new cells. If there is a lot of data to be organized, for example, a foreign trade company employee has both Chinese and English names, but they are mixed together when they are originally entered, and they need to be extracted separately. 1) What should I do?

1. Rapid separation of Chinese and English

In this case, the requirements are actually displayed in Chinese and English, so we can use the "separate" tool. By observing the original data, we can see that the Chinese and English names are separated by a half-width "(", use this as a column symbol. First, copy column A data into column B, click "Data → Columns", separate symbols "Other" half-width "(" (Figure 2).

Then separate the target selection B column, click "complete" to complete the separation. However, the Chinese in column C will still have a half-width ")", and the search and replace tool can be used to eliminate it (Figure 3).

hint:

The column-by-column method is used to find out the uniform delimiter in the original data. If the original data already exists, it is directly referenced (as in the above example). If the original data is not available, you need to add it manually. For example, you can insert a half-width space between Chinese and English. Use it as a split symbol.

2. Clever use of functions in Chinese and English automatic extraction

The column-by-column method requires a uniform split character between Chinese and English characters. If there is no similar symbol, the built-in functions LENB and LEN can also be used to automatically extract Chinese and English characters in Excel. Select B2, enter "=LEFT(A2,LENB(A2)-LEN(A2))" at it and enter =RIGHT(A2,2*LEN(A2)-LENB(A2)) at C2, then pull the formula down Fill it (Figure 4).

Formula explanation:

Here LENB function returns the number of bytes of the specified cell, LEN function returns the number of characters in the cell (note the difference between the two, such as A1 content is "1 computer", then the number of bytes is "7" (the number 1 word Section + three Chinese characters 6 bytes), the number of characters is "4" (1, Taiwan, electricity, brain four characters). Chinese characters and English are different, a Chinese character is 2 bytes (full-width symbol is also 2 bytes ), 1 English character is 1 byte. Suppose a cell has x letters and y characters, then LEN=x+y and LENB=x+2y. After solving the equation, you can get the number of characters. =LENB-LEN, the number of letters x=2*LEN-LENB.

For example, in the above example, "=LEFT(A2,LENB(A2)-LEN(A2))" is the number of Chinese characters to be intercepted from the left by the LEFT function, where LENB(A2)-LEN(A2)=12-9 = 3, so intercept three characters "Fang Lishen." Similarly, in the "=RIGHT(A2,2*LEN(A2)-LENB(A2))" function, 2*LEN(A2)-LENB(A2)=18-12=6, that is, 6 interceptions from the right. The character "(Alex)". Because the number of bytes in half-width and full-width signs is different, it should be noted here that full-width input symbols (such as parentheses) are recognized as double-byte characters, so the symbol like the parentheses in the above example must be converted to Half-width characters, otherwise the extraction will go wrong.

Since the final extracted English name contains parentheses, continue to enter the formula "=SUBSTITUTE(SUBSTITUTE(D2,"(",),")",)" in E2. This formula means to remove the left and right parentheses respectively. Note the double quotes. Whether the parenthesis is Chinese or half-width, you can use this formula to remove half-width, full-width, etc. symbols (Figure 5).

In the above example, the front, middle and back sides are mixed. If it is the former middle or middle age, only the LEFT and RIGHT functions need to be swapped. If these two methods are mixed together, you can use sorting to sort Chinese and English, and then use the above methods to extract them (Figure 6).

3. In both English and Chinese as needed

If the above formula needs to extract data in multi-case mixed Chinese and English data, we need to sort it in advance to complete it. In this case, we need to input different extraction formulas according to different situations. If you want to achieve a simpler operation, we can also use VBA script to achieve fast extraction.

Download the required code file from https://pan.baidu.com/s/1mioKSFu, then press Alt+F11 to quickly open the Visual Basic Editor, click "Insert → Module", and copy the downloaded code to the module. (Figure 7):

Click Save to save the file as a macro-enabled workbook and enable macro settings. Then open the workbook again, enter the formula “=SplitStringChs(A2)” in B2, and enter “=SplitStringeng(A2)” in C2. This formula is the macro created in the above script, so that it can be filled in B and C. Chinese and English characters are extracted freely, no matter what kind of mixture (Figure 8).