提取中英文数字字符两招搞定！ / 开普饭

牡丹（丙烯）

前言

当中文英文数字写在一个单元格时如何分开？这是很多人经常遇见的问题，条目不太多时，一般手动处理了事，条目多了如何去做，撸起袖子加油干，还是静下心来想一想有没有更好的方法？

我们不讨论太复杂的情况，仅讨论任何两者混合的情况，如下：

中文+英文，

中文+数字，

英文+中文，

数字+中文，

英文+数字，

数字+英文

上述六种情况，归纳起来只要学会两招即可搞定。

第一招可以对付上述前四种情况，即中文和其它字符混合，不论是数字还是英文字母，因为中文和其它字符不同的是，中文是双字节字符，其它为单字节字符。利用这个规律可以确定中文字符的个数。

这里介绍一下跟字符相关的函数，注意在函数中有一些后面带B的函数，如LEFTB,LENB等等，如果后面带B，则把一个中文字符当做两个来字节长度来看，不带B的函数则认为是一个字节长度。

LEN(文本)：取文本字符串的长度，每一个字符长度为1。

LENB(文本)：取文本字符串的长度，每个中文字符长度为2，其它字符为1。

RIGHT(文本,N)：取字符串右边N个字符

LEFT(文本,N)：取字符串左边N个字符

SUBSTITUE(文本,文本1,文本2)：把文本中文本1的字符串替换成文本2

我们假设一个文本字符串中有X个中文，Y个其它字符，那么可以有如下关系：

LENB(文本)=2X+Y

LEN(文本)=X+Y

解方程：

X=LENB(文本)-LEN(文本)

Y=2LEN(文本)-LENB(文本)

知道中文字符和其它字符的个数，我们就可以根据字符在左或右，用LEFT或RIGHT函数求出对应的中文或其它字符。也可以用SUBSTITUTE函数将字符串中的中文或英文替换成空值，取出另一半的字符。

第二招对应英文和数字的组合，因为英文和数字都是单字节字符，无法从字节上下手,但LOOKUP函数为我们提供了可能，在此向LOOKUP函数致敬。

常用的公式如下：

数字在左：

提取数字的公式：=LOOKUP(9E+307,--LEFT(文本,ROW($1:$100)))

数字在右：

提取数字的公式：=LOOKUP(9E+307,--RIGHT(文本,ROW($1:$100)))

英文的字符长度等于字符串的长度-数字的长度，用LEFT或RIGHT函数求出。

解释：

ROW($1:$100)：是取1到100行的行号，返回值为{1;2;3;4;…100}的数组，这里100是随意写的数，只要大于文本字符的长度即可。

LEFT(文本,ROW($1:$100))是一个数组公式，意思是取文本字符左边1,2,3,…100位数，组成一个数组。

如：LEFT(“1234ABC”,ROW($1:$7))则返回值为{1;12;123;1234;1234A;1234AB;1234ABC}

--：为负负得正的意思，即把文本强制性转换成数字，也可以在后面*1

=LOOKUP(9E+307,LEFT(文本,ROW($1:$100))*1)

LOOKUP(9E+307,数组)：返回数组中最后一个数字。

9E+307：为9乘以10的307次方，是一个非常大的数值，目的是在数组中取出最后一个数值，因为LOOKUP(数字1,数组)在数组中找不到数字1时，会返回数组中的最后一个数字。

END