excel将中英内容分开

excel将中英内容分开

本来是想分离中日英内容的，或者某网站的日/英日文释义，网上搜索了一番看到下面这个，于是兴冲冲地开始分解公式，最后才发现这个原理是按照中英文字符字节不相等写出来的，没法实现我想要的效果呢。不过这么长时间也不能白费，分解如下：

假如A2单元格中包含中英文混合内容，可以用下面的方法来进行分离：

1.使用用公式：

在B2单元格中输入数组公式：

=MID(A2,MATCH(2,LENB(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0),LENB(A2) -LEN(A2))

公式输入完毕后按Ctrl+Shift+Enter结束。

在C2单元格中输入公式：

=TRIM(SUBSTITUTE(A2,B2,""))

2.使用自定义函数：

还可以在VBA中自定义一个函数，然后在Excel表格中使用该函数进行分离。方法是：按Alt+ F11，打开VBA编辑器，单击菜单“插入→模块”，在右侧的代码窗口中输入下列代码：

Function SplitStringChs(TheString)

Dim n, Chs

For n = 1 To Len(TheString)

If Asc(Mid(TheString, n, 1)) < 0 Then

Chs = Chs& Mid(TheString, n, 1)

End If

Next

SplitStringChs = Chs

End Function

Function SplitStringEng(TheString)

Dim n, Eng

For n = 1 To Len(TheString)

If Asc(Mid(TheString, n, 1)) >= 0 Then

Eng = Eng& Mid(TheString, n, 1)

End If

Next

SplitStringEng = Trim(Eng)

End Function

关闭VBA编辑器，在B2单元格中输入公式：

=SplitStringChs(A2)

在C2单元格中输入公式：

=SplitStringeng(A2)

——————————————————————————————————————————————————

1.截取字符串的一部分，使用mid函数

假设A2为学生student，中文为学生，2字符，4字节，英文为student，7字符，7字节

公式外层是mid函数，该函数格式为：

MID(文本，开始位置，长度）

对于公式为：

MID(A2,MATCH(2,LENB(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0),LENB(A2)-LEN(A2))意思就是从A2这个字符串中，从黄色位置开始，提取粉色长度。

2.长度=A2字节数-A2字符数

长度=11-9=2

3.拆散字符串

下面公式取出字符串中的每一粒字符：

MID(A2,1,1)="学"

MID(A2,2,1)="生"

……

MID(A2,8,1)="n"

MID(A2,9,1)="t"

下面公式返回的是拆散后的一串字符(数组)：

MID(A2,{1;2;3;4;5;6;7;8;9},1)={"学";"生";"s";"t";"u";"d";"e";"n";"t"}

其中{1;2;3;4;5;6;7;8;9}用ROW(INDIRECT("1:"&LEN(A2)))计算所得。

在编辑栏中，请用鼠标选取MID(A1,ROW(INDIRECT("1:"&LEN(A2))),1)然后按F9可以看到上面的结果。

LENB({"学";"生";"s";"t";"u";"d";"e";"n";"t"})={2;2;1;1;1;1;1;1;1}

MATCH(2,{2;2;1;1;1;1;1;1;1},0)=1 找到中文起始位置

是在{2;2;1;1;1;1;1;1;1}里面找到与2相同的字符位置，也就是第一位

4.最终MID（A2,2,2)，也就得出学生二字,输出在B2中。

5.=TRIM(SUBSTITUTE(A2,B2,""))

SUBSTITUTE（A2,B2,"")=

SUBSTITUTE(text，old_text，new_text，instance_num)也就是将A2中的B2用空字符替换掉，剩下的就是英文部分了

再去掉其中的空格

——————————————————————————————————————————————————

ROW(INDIRECT("1:"&LEN(A1)))返回{1,2,...,len(a1)}数组

INDIRECT("1:"&LEN(A1))返回1至len(a1)行，row取行号，len(a1)是a1文本长度。

INDIRECT函数返回文本字符串所指定的引用,就是用文本描述单元格(或单元格区域)的名称.

它有两个参数,第一个为引用,第二个为类型.其中第二个参数可省(此时为A1样式引用).

INDIRECT("a"&5*(ROW()-1)+COLUMN()-1)

公式先用ROW()得到当前单元格的行号,用COLUMN()函数得到当前单元格的列号,再用5*(ROW()-1)+COLUMN()-1得到一个数值与字符"A"连接成一个单元格地址.

假定当前单元格为C5,公式结果:

=INDIRECT("a"&5*(5-1)+3-1)

=INDIRECT("a"&22)

=A22

MATCH( )

用途：

返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH 函数。注意：MATCH函数返回lookup_array 中目标值的位置，而不是数值本身。

语法：

MATCH(lookup_value，lookup_array，match_type)。

参数：

Lookup_value为需要在数据表中查找的数值，它可以是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。

Lookup_array是可能包含所要查找的数值的连续单元格区域，Lookup_array可以是数组或数组引用；

Match_type 为数字-1、0或1 ，它说明Excel 如何在lookup_array 中查找lo okup_value。

如果match_type 为1，函数MATCH 查找小于或等于lookup_value 的最大数值。如果match_type 为0，函数MATCH 查找等于lookup_value 的第一个数值。

如果match_type 为-1，函数MATCH 查找大于或等于lookup_value 的最小数值。