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

In daily work, it's common to deal with mixed Chinese and English data, especially in fields like international trade where names are often entered together. When you need to separate these for clarity or further processing, manually copying and pasting can be time-consuming. For example, if an employee has both a Chinese and an English name combined in one cell, how do you efficiently extract them?

1. Quick Separation of Chinese and English

When the data is structured with a clear delimiter, such as a half-width “(”, you can use Excel’s "Text to Columns" feature. Copy the original data into a new column, then go to "Data → Text to Columns". Choose "Other" and enter the “(” symbol. This splits the content into two columns. After splitting, the Chinese part may still contain a closing bracket, which can be removed using the "Find and Replace" function (Figure 3).

Tip: If there's no consistent delimiter, you can insert a space between Chinese and English manually. This makes it easier to split later.

2. Using Built-in Functions for Automatic Extraction

When no clear separator exists, Excel functions like LENB and LEN can help. The LENB function counts bytes, while LEN counts characters. Since Chinese characters take two bytes, this difference can be used to identify and extract them. For instance, entering `=LEFT(A2, LENB(A2)-LEN(A2))` in B2 will extract the Chinese part, and `=RIGHT(A2, 2*LEN(A2)-LENB(A2))` will get the English part (Figure 4).

Explanation: If a cell contains x English letters and y Chinese characters, LEN returns x + y, and LENB returns x + 2y. By solving these equations, we can determine the number of characters. For example, if LENB(A2) - LEN(A2) = 3, that means three Chinese characters are on the left. Similarly, the right side can be extracted based on the remaining bytes. Be sure to convert full-width symbols like parentheses to half-width before extraction to avoid errors.

3. Removing Extra Characters with SUBSTITUTE

After extracting, the English name might still include parentheses. Use the formula `=SUBSTITUTE(SUBSTITUTE(D2,"(", ""), ")", "")` to remove them. This works for both half-width and full-width brackets (Figure 5).

4. Advanced Extraction with VBA Scripts

For more complex cases, using VBA scripts can automate the process. Download the script from [this link](https://pan.baidu.com/s/1mioKSFu), open the Visual Basic Editor (Alt+F11), insert a module, and paste the code. Save the file as a macro-enabled workbook, enable macros, and use formulas like `=SplitStringChs(A2)` and `=SplitStringEng(A2)` to extract Chinese and English parts quickly (Figure 8).