Solved

How to split excel cell content by character encoding

Posted on 2014-11-15
3
881 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 50

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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 …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
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…

726 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