Link to home
Start Free TrialLog in
Avatar of dtview
dtview

asked on

How to split excel cell content by character encoding

I have an excel spreadsheet that contains a column "Name". The column contains name in English followed by name in Chinese character. For example:
Chang Chingting 章经亭
Lee Tao李涛

I need to split the column so that "Chang Chingting" and "Lee Tao" belong to one column, and "章经亭" and "李涛" belong to another column. Excel split-cell by delimiter does not work, since in between the English and Chinese there isn't necessarily a space (this was created by end user). The names always start with English and end in Chinese characters. Each name can be between 2 to 4 Chinese characters.

I am not familiar with excel vba. Right now I am thinking to split the column based on character encoding. Is that possible?



Thanks
Sample.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dtview
dtview

ASKER

For the sake of this sample excel sheet the solution is perfect, although for my real life data some of the   Chinese characters actually slipped into the "strFirstCell". To be fair, this solution is accepted.
Avatar of dtview

ASKER

As mentioned in another post, with test data it is perfect, with production data some of the Chinese characters managed to slip into the "English" column. For the sake of those who need similar solutions do take note that further troubleshooting may be required.