Separating Chinese and English Description into 2 cells

[Satoshi]

Hello,

I have an inventory list of items, that combines both chinese and english descriptions into it.
Is there anyway I can separate them using formulas?

The ??? are the chinese characters which may appear before or after then english description.

Thank you

Sheet1

	A	B	C	D
1
2	Material Description	Specifications	English Description	Chinese Description
3	???????? Low voltage reactive compensation device	G68
4	????? Power distribution box	X94
5	????? Power distribution box	X56
6	Electrical cables ????	Y95
7	Electrical cables ????	Y72
8	Electrical cables ????	Y30
9	Electrical cables ????	Y90
10	??? Sheath line	B53
11	?? yarn	R63
12	????? Single stranded aluminum strand	B34
13	???? Communication cable	M2
14	???? Local fan blower	556
15	???? Local fan blower	149
16	?? Wind belt	?23
17	?? Wind belt	?35
18	???? Wind belt adjusting	53
19	??? Blaster	2100
20	?? Air pick	G24
21	???? Air pick drill	G71
22	?? Anchor bolt	?99
23	??? rubber pad	F72
24	??? Check back cushion	F12
25	??? Ratchet wheel hand	Y2
26	?? Trigger	Y22
27	???? Chuck bolt	Y10
28	??? Guide sleeve	Y78
29	?? Chuck	Y35
30

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:353.6px;"><col style="width:260.8px;"><col style="width:174.4px;"><col style="width:348.8px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[thisoldman]

What I can dream up, using built-in Excel functions, are monsters in length.

I can offer these two User Defined Functions:

Function TrimNonLatin(txt) As String
''''
' Removes all characters U+0370 and higher from txt.
''''
    Dim glyph As String
    Dim i As Long
    
    For i = 1 To Len(txt)
        glyph = Mid(txt, i, 1)
        If glyph < ChrW(&H370) Then
            TrimNonLatin = TrimNonLatin & glyph
        End If
    Next i
End Function
''''


Function TrimLatin(txt) As String
''''
' Removes all characters U+036F and below from txt.
''''
    Dim glyph As String
    Dim i As Long
    
    For i = 1 To Len(txt)
        glyph = Mid(txt, i, 1)
        If glyph > ChrW(&H36F) Then
            TrimLatin = TrimLatin & glyph
        End If
    Next i
End Function

To create a user defined function:

1. While in a copy of the workbook where you want to add the function, press Alt+F11. The visual basic editor should open.
2. From the editor menu, select Insert > Module.
3. Copy and paste the code above into the module.

Save the file in a macro-enabled format, either as an xlsm or xlsb format file.

To use:
In C3, enter =TrimNonLatin(A3) and copy downward.
In D3, enter =TrimLatin(A3) and copy downward.

Notes:
These functions are not perfect. Trailing and leading spaces may remain in the resulting strings. The Chinese results string may contain Chinese parentheses and commas that were embedded within the Latin portion of the source string.

Here's an example string taken from the Chinese language Wikipedia:

A3	並另外命名為東亞編碼字符（East Asia Coded Character，EACC，ANSI/NISO Z39.64）
C3	East Asia Coded CharacterEACCANSI/NISO Z39.64
D3	並另外命名為東亞編碼字符（，，）

<tbody>
</tbody>