Want to make Spanish and Chinese words and phrases, how the Spanish and Chinese will be automatically divided into two columns, with regular or Excel how to solve? For example the format below:

Inobservancia to comply with the
Interrogatorio questioning
Irretroactividad not back before sex
Defend alegacion
Alta traicion treason
Alteracion del orden disturbing the peace
Anteproyecto bill
Anulacion abolished
Apelar appeal
Recurrir appeal
Aplicacion DE la ley, law enforcement
Audiencia publica trial
Audiencia trial

CodePudding user response:

Suppose you a row in cell A1, suggest to try with the following formula,

LEFT (A1, LENB (A1) - LEN (A1))
RIGHT (A1, LEN (A1) * 2 - LENB (A1))

Practical principles is that LENB returns number of bytes, LEN returns the number of characters, their difference is the length of the string in Chinese, through the left or right separation,
If the formula is invalid, and you excel to set the default language,

CodePudding user response:

Under the excel version, Left shows is empty, Right to show Chinese and Spanish, under the excel the spanish-language version, Left and Right are unable to identify, display a question mark,

CodePudding user response:

There are people to save post??

CodePudding user response:

Can only write the custom formula, and find out the location of the first Chinese characters, shard, because some Spanish characters also fell on the characters of western language character, also subdivided,
Or may be considered in UEdit32 spin-off, but watch just now, as if also is not reliable,

CodePudding user response:

引用 2nd floor  xiao 的回复:

在excel中文版下,Left显示为空,Right展示中文和西语,在excel西语版下,left和right都无法识别,展示问号,

The original poster is in reply to me? Reply post after the proposal point of reference, so others can get message, I didn't pass, is to look at the post just came in to see, otherwise I still don't know you this is invalid! If not, you should use VBA, night back to your code,

CodePudding user response:

1. Open the excel, at the same time, press the Alt + F11, pop-up window is the VBA environment,
2. On the left side of the partial insertion module interface,

3. Double-click to open the module, attention must be open module, paste the code below:
4. Back to excel spreadsheet interface, in the target cell, input=CN (A1), after return will extract Chinese characters,

Function CN (CH As String) As String
Dim Chinese As Object
The Set Chinese=CreateObject (" VBSCRIPT. REGEXP ")
Chinese. The Pattern="[^ \ u4e00 - \ u9fa5]"
Chinese. The IgnoreCase=True
Chinese. Global=True
CN=Chinese. Replace (CH, "")
The Set of Chinese=Nothing
End the Function


Extraction function is realized in "^ \ u4e00 - \ u9fa5", actually on behalf of the Chinese characters of regular expression, such as extraction of Spanish, please find the relevant regular,

CodePudding user response:

refer to 6th floor nangongxiaobai response:

1. Open the excel, at the same time, press the Alt + F11, pop-up window is the VBA environment,
2. On the left side of the partial insertion module interface,

3. Double-click to open the module, attention must be open module, paste the code below:
4. Back to excel spreadsheet interface, in the target cell, input=CN (A1), after return will extract Chinese characters,

Function CN (CH As String) As String
Dim Chinese As Object
The Set Chinese=CreateObject (" VBSCRIPT. REGEXP ")
Chinese. The Pattern="[^ \ u4e00 - \ u9fa5]"
Chinese. The IgnoreCase=True
Chinese. Global=True
CN=Chinese. Replace (CH, "")
The Set of Chinese=Nothing
End the Function


Extraction function is realized in "^ \ u4e00 - \ u9fa5", actually on behalf of the Chinese characters of regular expression, such as extraction of Spanish, please find the relevant regular,

Spanish and other Latin languages is the same as regular, like English, you will help me to do with the

CodePudding user response:

引用 7th floor  晓的回复:

Quote: refer to 6th floor nangongxiaobai的回复:

1.打开excel,同时按下alt+F11,弹出的窗口即为VBA环境,
2. On the left side of the partial insertion module interface,

3. Double-click to open the module, attention must be open module, paste the code below:
4. Back to excel spreadsheet interface, in the target cell, input=CN (A1), after return will extract Chinese characters,

Function CN (CH As String) As String
Dim Chinese As Object
The Set Chinese=CreateObject (" VBSCRIPT. REGEXP ")
Chinese. The Pattern="[^ \ u4e00 - \ u9fa5]"
Chinese. The IgnoreCase=True
Chinese. Global=True
CN=Chinese. Replace (CH, "")
The Set of Chinese=Nothing
End the Function


Extraction function is realized in "^ \ u4e00 - \ u9fa5", actually on behalf of the Chinese characters of regular expression, such as extraction of Spanish, please find the relevant regular,

Spanish and other Latin languages is the same as regular, like English, you will help me to do with the

Spanish acronym ES to Spain - Spanish

CodePudding user response:

Function ES - As String) As String
Dim Sp As Object
The Set Sp=CreateObject (" VBSCRIPT. REGEXP ")
Sp. The Pattern="[^ ^ A - A - Z Z]"
Sp. The IgnoreCase=True
Sp. Global=True
ES=Sp. Replace (-, "")
The Set of Sp=Nothing
End Function

CodePudding user response:

Alt + f11 shortcuts on my computer doesn't work, how to change the way into the VBA environment

CodePudding user response:

引用 10th floor  xiao 的回复:

alt+f11快捷键在我电脑不好用呢,怎么换个方式进入VBA环境呢

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull