Solved

How to split excel cell content by character encoding

Posted on 2014-11-15
3
801 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 49

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to create a drop-down list in Excel 9 45
Excel: getting text to wrap 3 94
why can't I sort this by internment date 1 48
Finding a closest match in Excel 7 49
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 …
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

832 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