Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

How to split excel cell content by character encoding

Posted on 2014-11-15
832 Views
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
0
Question by:dtview
• 2

LVL 50

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40444337
Hi,

pls try

``````Sub macro()

For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Idx = 1 To Len(c.Value)
If AscW(Mid(c.Value, Idx, 1)) <= 255 And Done = False Then
strFirstCell = strFirstCell & Mid(c.Value, Idx, 1)
End If
If AscW(Mid(c.Value, Idx, 1)) > 255 Then
Done = True
End If
If Done Then
strSecondCell = strSecondCell & Mid(c.Value, Idx, 1)
End If
Next
Range("G" & c.Row) = Trim(strFirstCell)
Range("h" & c.Row) = strSecondCell
strFirstCell = ""
strSecondCell = ""
Done = False
Next
End Sub
``````
Regards
Sample--5-V1.xlsm
0

Author Comment

ID: 40445546
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.
0

Author Closing Comment

ID: 40445548
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.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.