ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

1 Q & A posts tagged with Chinese

Mon
Apr 13
2009

Separate English and Traditional Chinese (excel file) into 2 columns without special characters (; or : or *)?

Hello there:

Hope u can help me...

I have a huge list of Buddhist terms (20,000) in Traditional Chinese, together with the English definition for each of the 20,000 entries. Problem: all of the terms are listed in a single column, with the english definition coming before every Traditional Chinese concept: what I try to mean with this is that some english definitions involve more than one single word (some definitions have 23 words as a whole), and the same concept, but in Chinese, might involve more than just one character (up to 7).

I cannot use a "space" to separate the text into different columns, because of the amentioned reason, there are not special characters that separate the English of the Chinese (such as ; or : or *), its all like the following sample:


9-11 am ??
a (walled) city ?
a bed ??
a day without work is a day without food ????????
a heap ?
a ko?i of nayutas ?????
a man ??
a thread, a butt ????
a wild fellow ???

like this, all the way up to 20,000

So, do u know of any way I can separate the whole text into different columns, based on the used script (Chinese and English)?

I asked some Computer science teachers at a local university: they suggested to create a macro that differentiates the script in use according to ASCII (english letters come with very low numbers, while Traditional Chinese must involve really big numbers, or so they said), but, I confess that my knowledge of computing is extremely low (main reason Im contacting u, btw...)

Hope u read this message, understand my broken english, and give me an answer...

Thanks a lot

Cheers


Answer

You don't need a macro to do that
I just fixed something like this (in pure functions) few days ago, it was about changing CamelCase to Proper, here is the file in case you are interested

Modifying that file, you can do something like this

If you have the cell in B4, paste this in E4

=IF(OR( COLUMN()-4>LEN( $B4),SUM( $D4:AC4)>0),"",IF(CODE( MID( $B4,COLUMN( )-4,1))>150,COLUMN( )-4,""))
Then copy and paste up to column AD (to get the possibilities of 26 characters)
Now in cell AE4, paste this to get the left side of the text (Before the first Chinese char)
=LEFT(B4,MAX(E4:AD4)-1)

And this in nAF4 to get the right part (after the first Chinese char)
=MID(B4,MAX( E4:AD4)+1,500)


Good luck with that, let me know if you didn't understand it or you want to edit it

VBAXLMan is here to fill your Excel needs

Monday 4/13/2009 12:09:05 PM

Tags

History