Separating Chinese and English Description into 2 cells

S

Satoshi

New Member
Joined
Feb 1, 2018
Messages
1
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

ABCD

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]Material Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]Specifications[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]English Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]Chinese Description[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]????????
Low voltage reactive compensation device[/TD]
[TD="align: center"]G68[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]?????
Power distribution box[/TD]
[TD="align: center"]X94[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]?????
Power distribution box[/TD]
[TD="align: center"]X56[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]Electrical cables ????[/TD]
[TD="align: center"]Y95[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]Electrical cables ????[/TD]
[TD="align: center"]Y72[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]Electrical cables ????[/TD]
[TD="align: center"]Y30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]Electrical cables ????[/TD]
[TD="align: center"]Y90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]???
Sheath line[/TD]
[TD="align: center"]B53[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]??
yarn[/TD]
[TD="align: center"]R63[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: center"]?????
Single stranded aluminum strand[/TD]
[TD="align: center"]B34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: center"]????
Communication cable[/TD]
[TD="align: center"]M2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: center"]???? Local fan blower [/TD]
[TD="align: center"]556[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: center"]???? Local fan blower [/TD]
[TD="align: center"]149[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]?? Wind belt[/TD]
[TD="align: center"]?23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: center"]?? Wind belt[/TD]
[TD="align: center"]?35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: center"]???? Wind belt adjusting[/TD]
[TD="align: center"]53[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: center"]??? Blaster[/TD]
[TD="align: center"]2100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: center"]?? Air pick[/TD]
[TD="align: center"]G24[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: center"]???? Air pick drill[/TD]
[TD="align: center"]G71[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: center"]?? Anchor bolt[/TD]
[TD="align: center"]?99[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]
[TD="align: center"]??? rubber pad[/TD]
[TD="align: center"]F72[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]
[TD="align: center"]??? Check back cushion[/TD]
[TD="align: center"]F12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]25[/TD]
[TD="align: center"]??? Ratchet wheel hand[/TD]
[TD="align: center"]Y2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]26[/TD]
[TD="align: center"]?? Trigger[/TD]
[TD="align: center"]Y22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]27[/TD]
[TD="align: center"]???? Chuck bolt[/TD]
[TD="align: center"]Y10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]28[/TD]
[TD="align: center"]??? Guide sleeve[/TD]
[TD="align: center"]Y78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]29[/TD]
[TD="align: center"]?? Chuck[/TD]
[TD="align: center"]Y35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]30[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
What I can dream up, using built-in Excel functions, are monsters in length.

I can offer these two User Defined Functions:

Code: 
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:

[TABLE="class: grid"]
<tbody>[TR]
[TD="align: left"]A3[/TD]
[TD="align: left"]並另外命名為東亞編碼字符（East Asia Coded Character，EACC，ANSI/NISO Z39.64）[/TD]
[/TR]
[TR]
[TD="align: left"]C3[/TD]
[TD="align: left"]East Asia Coded CharacterEACCANSI/NISO Z39.64[/TD]
[/TR]
[TR]
[TD="align: left"]D3[/TD]
[TD="align: left"]並另外命名為東亞編碼字符（，，）[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA Code to compare Dates, then run macro to match colums
Replies
4
Views
966
Airstar
A
Dazzawm
  • Question Question
Code To Remove Only 2 Rows
Replies
9
Views
991
Dazzawm
Dazzawm
B
  • Question Question
Excel Sumproducts with Row Number returned
Replies
4
Views
2K
Biz
B
B
  • Question Question
Excel VBA Solver Looping Multiple Columns but not updating References
Replies
0
Views
845
Biz
B
J
  • Question Question
Auto-Populate Cells Based on Input
Replies
3
Views
672
JRC_Design
J

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top