?
Solved

How to split excel cell content by character encoding

Posted on 2014-11-15
3
Medium Priority
?
1,034 Views
Last Modified: 2014-11-16
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
Comment
Question by:dtview
[X]
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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Open in new window

Regards
Sample--5-V1.xlsm
0
 

Author Comment

by:dtview
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

by:dtview
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question