Solved

How to split excel cell content by character encoding

Posted on 2014-11-15
3
723 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
  • 2
3 Comments
 
LVL 48

Accepted Solution

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a nutshell Dropbox is a cloud-based data storage service which synchronizes with data files on your computers. The Dropbox folk provide 2GB of free storage but if you need more you can sign up for a 50GB or 100GB subscription account. I pr…
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now