就是这么简单!秒提中英混排文字

平淡2017-10-18 15:35应用 标签:

在日常的工作中,我们经常会遇到中英文混杂的各种数据,为了文本规范需要将混杂的中、英文分别提取出来。常规的方法是分别复制其中内容粘贴到新的单元格,如果需要整理的数据很多,比如某外贸公司员工都有中英文名字,但是在原始录入时是混杂在一起,现在需要分别提取出来(图1),该怎么办?

1711A-NRTQ-1

1. 用活分列 快速分离中英文

本例要求实际上是将中、英文分列显示,因此我们可以使用“分列”工具实现。通过观察原始数据可以发现,中文和英文名称是通过一个半角的“(”隔开的,使用这个作为分列符号。首先将A列数据复制B列,点击“数据→分列”,分隔符号选择“其他→”半角的“(”(图2)。

1711A-NRTQ-2

接着分列目标选择B列,点击“完成”即可完成分离。不过C列的英文仍会带个半角的“)”,最后再使用查找替换工具进行消除即可(图3)。

1711A-NRTQ-3

小提示:

分列的方法关键找出原始数据中统一的分隔符号,如果原始数据已经有则直接引用(如上例),如果原始数据没有则需要手动添加,比如可以在中英文之间插入一个半角空格,并把其作为分割符号即可。

2. 巧用函数 中英文自动提取

分列的方法需要中英文字字符间有统一的分割字符,如果没有类似符号,在Excel中还可以使用内置函数LENB和LEN进行自动中、英文字符提取。选中B2,在其处输入“=LEFT(A2,LENB(A2)-LEN(A2))”,在C2输入=RIGHT(A2,2*LEN(A2)-LENB(A2)),然后将公式下拉填充即可(图4)。

1711A-NRTQ-4

公式解释:

这里LENB函数返回指定单元格的字节数,LEN函数则返回单元格的字符数(注意两者的区别,比如A1内容是“1台电脑”,那么字节数为“7”(数字1字节+三个汉字6字节),字符数为“4”(1、台、电、脑四个字符)。汉字和英文不同,1个汉字是2个字节(全角符号也是2个字节),1个英文字符是1个字节。假设某个单元格里有英文字母x个,汉字y个,那么LEN=x+y,LENB=x+2y,解方程后可以得到汉字个数y=LENB-LEN,字母个数x=2*LEN-LENB。

比如在上述实例中,“=LEFT(A2,LENB(A2)-LEN(A2))”,就是通过LEFT函数从左开始截取汉字个数,这里LENB(A2)-LEN(A2)=12-9=3,所以截取三个汉字“方力申”。同理,“=RIGHT(A2,2*LEN(A2)-LENB(A2))”函数中,2*LEN(A2)-LENB(A2)=18-12=6,即从右边开始截取6个字符“(Alex)”。因为半角和全角符号的字节数不同,这里要注意的是全角输入的符号(如小括号)等会被识别为双字节字符,因此像上述实例中的小括号的符号一定要预先转为半角字符,否则提取会出错。

由于最终提取出来的英文名称包含小括号,继续在E2输入公式“=SUBSTITUTE(SUBSTITUTE(D2,"(",),")",)”,这个公式的意思是分别去掉左右括号,注意双引号中的括号是中文还是半角,可以用此公式分别去除半角、全角等相应的符号(图5)。

1711A-NRTQ-5

上述实例中是前中后英混杂,如果是前英后中,则只需将LEFT和RIGHT函数对换即可。如果是这两种方式混杂在一起,则可以先使用排序方式,将中英文排序,然后分别使用上述方法进行提取即可(图6)。

1711A-NRTQ-6

3. 不分彼此 按需提取中英文

上述公式如果要在多情况混杂中英文数据中提取数据,我们就需要预先进行排序才可以完成,这样需要分别根据不同情况输入不同提取公式。如果要实现更简单的操作,我们还可以借助VBA脚本实现快速提取。

到https://pan.baidu.com/s/1mioKSFu下载所需的代码文件,然后按Alt+F11键快速打开Visual Basic编辑器,点击“插入→模块”,将下载到的代码复制到模块中(图7):

1711A-NRTQ-7

点击“保存”,将文件保存为“启用宏的工作簿”并启用宏设置。这样再次打开工作簿,在B2输入公式“=SplitStringChs(A2)”、C2输入“=SplitStringeng(A2)”,这个公式就是我们上述脚本里制作的宏,这样向下填充后就可以在B、C中自由提取中英文字符了,无论是哪种混杂(图8)。

1711A-NRTQ-8