+ Reply to Thread
Results 1 to 14 of 14

Split Chinese and Enlish three columns

  1. #1
    Registered User
    Join Date
    12-23-2019
    Location
    South Africa
    MS-Off Ver
    Ver 16.31 Mac
    Posts
    5

    Split Chinese and Enlish three columns

    Hi my problem starts with text to columns not working with chinese text. I found a solution to split English and Chinese in two columns. The challenge is that it only works when English is first. I need to split a string into three columns starting with Chinese first - see sample

    中国第一汽车集团有限公司一汽牌CA7001BEVB纯电动轿车

    See the code that works for english first. Please see how this could be modified to split my sample in three columns thanks
    Please Login or Register  to view this content.
    Last edited by AliGW; 12-24-2019 at 03:09 AM.

  2. #2
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Split Chinese and Enlish three columns

    Hi

    Is your combine English/Chinese text got fix pattern, for e.g. the english code always start with CAXXXXX, or the original string can separate by delimiter comma, space etc
    中国第一汽车集团有限公司一汽牌.CA7001BEVB, 纯电动轿车

    then this can be easily done using Power Query, attached sample is assume the starting position is fixed (just an illustration)
    Attached Files Attached Files
    Christopher Yap

  3. #3
    Registered User
    Join Date
    12-23-2019
    Location
    South Africa
    MS-Off Ver
    Ver 16.31 Mac
    Posts
    5

    Re: Split Chinese and Enlish three columns

    Thanks but unfortunately the English length and letters are not consistant

  4. #4
    Registered User
    Join Date
    12-23-2019
    Location
    South Africa
    MS-Off Ver
    Ver 16.31 Mac
    Posts
    5

    Re: Split Chinese and Enlish three columns

    东风汽车股份有限公司东风牌dfa5040xdwbev纯电动流动服务车
    东风汽车股份有限公司东风牌dfa5040xxytbev2纯电动厢式运输车
    东风汽车集团有限公司东风雷诺牌eq7000fr2f1bev纯电动轿车
    东风汽车集团有限公司东风牌eq7000fl2f1bev纯电动轿车
    东风汽车集团有限公司风神牌eq7000fa2f1bev纯电动轿车
    东风汽车集团有限公司俊风牌dfa7000a1fbbev纯电动轿车
    东风汽车集团有限公司俊风牌dfa7000l2abev6纯电动轿车
    东风汽车集团有限公司启辰牌eq7000fv2f1bev纯电动轿车
    东风汽车有限公司启辰牌dfl7000na62bev纯电动轿车
    Last edited by AliGW; 12-24-2019 at 03:08 AM. Reason: Please don't quote unnecessarily!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,094

    Re: Split Chinese and Enlish three columns

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Split Chinese and Enlish three columns

    Hi Ev Genius,

    It can be done quite easily by Power Query as long as there is a "牌" in the 1st Chinese string, and "纯" in the second Chinese string

    Attached is the result
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Split Chinese and Enlish three columns

    Assuming the data is in col.A
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    12-23-2019
    Location
    South Africa
    MS-Off Ver
    Ver 16.31 Mac
    Posts
    5

    Re: Split Chinese and Enlish three columns

    Quote Originally Posted by jindon View Post
    Assuming the data is in col.A
    Please Login or Register  to view this content.
    Thanks this seem to work

  9. #9
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,441

    Re: Split Chinese and Enlish three columns

    Here is another macro that you can consider...
    Please Login or Register  to view this content.
    Note: I have assumed the vertical bar symbol (|) will never be part of your text. If it could be, then change the four red highlighted text characters to a character that is guaranteed never to be in your text.
    Last edited by Rick Rothstein; 12-25-2019 at 02:51 PM.

  10. #10
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,203

    Re: Split Chinese and Enlish three columns

    To collection ... ...
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: Split Chinese and Enlish three columns

    Sorry, I neglect MAC.
    Attached Files Attached Files
    Last edited by ikboy; 12-26-2019 at 08:50 PM.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Split Chinese and Enlish three columns

    With CreateObject("vbscript.regexp")
    Mac doesn't support ActiveX object other than object on the user form.

    That's a well know issue...

  13. #13
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,203

    Re: Split Chinese and Enlish three columns

    M(issing)A(ctiveX)C(ontrols) ? ...


    Edit:

    ... AscW problems may also occur ... : (
    Last edited by mjr veverka; 12-26-2019 at 10:15 AM.

  14. #14
    Registered User
    Join Date
    12-23-2019
    Location
    South Africa
    MS-Off Ver
    Ver 16.31 Mac
    Posts
    5

    Re: Split Chinese and Enlish three columns

    Quote Originally Posted by Rick Rothstein View Post
    Here is another macro that you can consider...
    Please Login or Register  to view this content.
    Note: I have assumed the vertical bar symbol (|) will never be part of your text. If it could be, then change the four red highlighted text characters to a character that is guaranteed never to be in your text.
    Thanks Rick, much appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Separating data column with English and Chinese Text into 2 separate columns
    By bkzd1989 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-09-2023, 08:46 AM
  2. split the chinese character and english to different columns
    By ks1102 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-13-2023, 06:22 AM
  3. [SOLVED] 2 columns data split to more columns based on groups in 2nd column
    By vendam in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2019, 05:43 AM
  4. [SOLVED] Sum ifs for Multiple columns/ Split columns
    By FRIEL in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-28-2018, 12:23 PM
  5. Formula(s) to Split Text String Across Columns Using Columns as Seperator
    By WaylettChris in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2016, 01:13 PM
  6. [SOLVED] Format Spreadsheet - split text columns, rearrange columns, conditional format
    By lacke81c in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2015, 11:50 AM
  7. Split uneven data from a single columns to multiple columns
    By pfoz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-13-2013, 08:24 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1