上周帮朋友处理一份销售团队的通讯录里面数据的格式五花八门“张伟ZhangWei 13800138000”“LiLei李雷 13912345678”“韩梅梅Han Meimei 13700001111”中文名、拼音名、手机号全部挤在同一格。他想让我按姓氏统计团队里姓张、姓李、姓王的各有几个。我一开始想用LEFT直接从单元格左侧把姓氏切出来结果遇到“LiLei李雷”这种首字符不是中文的格式时直接栽了跟头。后来我用CODE函数做了一套“逐字符扫描”的清洗套路不到二十分钟就把整张表处理干净了。这个函数平时确实不起眼但在中英文分离、字符类型判断、姓名统计这类任务里它就是隐藏的利器。这篇文章我会把这个套路完整拆开从函数原理到公式写法从基础案例到进阶统计一次讲透。无论你是运营、HR、财务还是数据分析师只要工作中会碰到中英混排的表格接下来这些内容都能直接抄作业。1. CODE函数的秘密每个字符都有一串数字暗号1.1 CODE到底返回什么它和CHAR是什么关系CODE的语法非常简单CODE(text)作用是返回文本字符串中第一个字符对应的字符代码。你给它一个“A”它返回65给它一个“z”返回122给它一个“0”返回48给它一个空格返回32。CHAR函数正好是它的反操作CHAR(65)返回“A”CHAR(97)返回“a”CHAR(12212)这种还能用来还原一个汉字。两个函数一正一反相当于字符和数字之间的“翻译官”。很多人容易忽略一个底层事实计算机里的字符并不是直接以“字母”“汉字”这种形态存储的而是存成一组数字编号。英文和数字长期占据ASCII编码中的1到127号位置而汉字由于数量庞大全部落在这个区间之外。所谓判断一个字符是中文还是英文本质上就是看它的数字编号落在哪个区间。CODE函数返回的恰好就是这个编号。理解这一层你会发现自己手里的工具突然变多了。因为不只是“中英文分离”判断字符是否为标点、是否为数字、是否为全角字符底层都是同一套思路拿到字符代码再判断代码落在哪个区间。1.2 中英文字符的代码分界点在哪里在简体中文Windows系统里一个汉字的GBK编码通常由两个字节构成CODE函数返回给我们的就是这个双字节编码转换出来的数值。具体数字在不同系统代码页下会有些差异但有一条规律非常稳定绝大多数中文字符的CODE返回值都会大于127而英文、数字、半角标点、空格、换行符的CODE返回值都在1到127之间。我常把它理解为一条水位线小于等于127的是ASCII字符大于127的是高位字符。用Excel公式表达就是CODE(字符)127代表“非英文”CODE(字符)127代表“英文或其他半角符号”。有个细节需要提醒这里说的是“半角”。如果你在表格里遇到全角字母、全角数字、全角括号比如“”“”“张伟”它们的CODE返回值同样大于127会被我们的简单判断误当成“中文”。这个坑我在后面的避坑章节里专门讲先留个印象。1.3 结合MID实现逐字符扫描只对单元格的第一个字符调用CODE没什么意思真正厉害的是把它和MID函数组合起来实现“逐字符体检”。MID(text, start_num, num_chars)可以从文本的任意位置切出指定数量的字符。配合一个从1到文本总长度的序号序列就能把单元格里的每个字符依次取出来再用CODE逐个判断身份。在Excel里构造“1到总长度”的序号序列传统且普遍的做法是ROW(INDIRECT(1:LEN(A2)))它会把INDIRECT(1:5)解析为对第1行到第5行的引用再通过ROW函数得到{1;2;3;4;5}这个横向数组。公式写起来稍微有点绕但这是老版本Excel中为数不多能动态生成序号数组的方法。这里还要澄清一个常见的误解Excel的LEN和MID都是按“字符”计数的不是按“字节”。所以“中”这个汉字LEN返回1MID也能正常把它切出来。只有LEFTB、LENB这类带字母B的老函数才按字节计算。你用MID处理中文时不需要担心“一个汉字占两个字节所以切一半”的问题。理解到这一步中英文分离的底层逻辑就通了逐字取出逐字判号按号归类最后拼接。2. 直击需求五招实现中英文分离2.1 方法一辅助列逐个判断适合新手理解和排查如果你刚开始接触这类问题我不建议一上来就写复杂的数组公式。先用辅助列把每个字符拆出来观察每一步的结果是建立手感最快的方式。假设A2单元格是“张伟ZhangWei”操作步骤如下在C1单元格输入“字符”在D1单元格输入“代码”在E1单元格输入“类型”。C2输入公式MID($A$2,COLUMN()-2,1)然后向右拖动填充直到把整串字符都拆完。公式中的COLUMN()-2在C列时等于1D列时等于2正好生成连续的序号。D列用CODE(C2)取出每个字符的代码。E列用IF(D2127,中,英)标记类型。最后另起一行用CONCAT(E2:P2)之类的方式把中文字符串起来。这个方案笨拙但价值在于每一步都看得见、摸得着。如果结果不对你一眼就能看出是哪个字符被分错了类适合拿来做教学和排查。处理少量数据时辅助列的方式并不比高级公式慢多少。2.2 方法二TEXTJOIN数组公式一条公式搞定当你对逐字判断的逻辑足够熟悉之后就可以把上面的辅助列过程压缩成一条数组公式。提取中文部分输入TEXTJOIN(,TRUE,IF(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))127,MID(A2,ROW(INDIRECT(1:LEN(A2))),1),))提取英文部分输入TEXTJOIN(,TRUE,IF(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))127,MID(A2,ROW(INDIRECT(1:LEN(A2))),1),))这两条公式的骨架完全一样。MID负责把每个字符抠出来CODE负责判断是中文还是英文IF负责决定保留哪个字符TEXTJOIN负责把所有保留下来的字符无缝拼成最终结果。注意两个关键点如果你是Excel 2019、WPS或早期Office版本输入完公式后需要按CtrlShiftEnter确认数组公式。确认成功后公式两端会出现花括号{}。如果你用的是Excel 365直接回车即可动态数组引擎会自动处理。ROW(INDIRECT(1:LEN(A2)))这段是在动态生成1到文本长度的序号序列。一定要用LEN来动态控制范围不要偷懒写成ROW($1:$99)。一旦文本长度超过99个字符后面的内容就会莫名丢失排查起来很费劲。2.3 方法三要求更高的场景里加入空格分隔上面方法二有个现实问题如果原文本是“张伟ZhangWei”这种中文在前、英文在后的格式提取出来的中文是“张伟”英文是“ZhangWei”各自独立看着很清爽。可如果是“我用Excel处理Python报表”这种中英交错、频繁轮换的文本按顺序拼接后中文部分会变成“我用处理报表”英文部分变成“ExcelPython”整个语义都散了。如果你希望在中英文切换的位置插入一个空格让结果变成“我用 处理 报表”和“Excel Python”这种分组式效果可以用下面这条进阶数组公式TRIM(TEXTJOIN(,TRUE,IF(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))127,IF(CODE(MID( A2,ROW(INDIRECT(1:LEN(A2))),1))127, ,)MID(A2,ROW(INDIRECT(1:LEN(A2))),1),)))这个公式的思路是提取当前字符时同时检查它前一个字符的代码。如果当前是中文而前一个是英文ASCII就在这个中文字符前面补一个空格如果当前是英文而前一个是中文处理方式同理。这样做相当于在语言类型切换的边界处自动插入了分隔符。不过我也要实话实说这种公式在日常工作中并不是最优解。它有两个小毛病一是当原文本本身就是中英频繁交错的句子时插出来的空格会比较多结果仍然偏碎二是公式太长别人接手维护时看得头大。所以我的一般建议是小数据量且有临时需求时可以用它遇到复杂文本或者需要长期跑的表直接上VBA自定义函数更省心。2.4 方法四Excel 365专属的清爽版本如果你用的是Excel 365可以把恼人的ROW(INDIRECT(1:LEN(A2)))换成全新的SEQUENCE函数公式马上清爽一个档次TEXTJOIN(,TRUE,IF(CODE(MID(A2,SEQUENCE(LEN(A2)),1))127,MID(A2,SEQUENCE(LEN(A2)),1),))SEQUENCE(LEN(A2))直接生成一个从1到LEN(A2)的等差数列语义清晰计算过程也不容易触发易失性函数。这是我在2024年之后处理表格时的首选写法。如果你还在用Excel 2016或者更老的版本也不用急着升级——ROW(INDIRECT(...))的写法虽然丑陋但非常稳定处理几千行数据毫无压力。2.5 方法五另一种灵活的实现——Power Query与VBA公式方案再方便也有绕不过去的短板公式本身不透明别人拿到你的表后很难快速理解。如果这是一个需要反复处理的固定流程我更推荐把数据丢给Power Query或者干脆写一个自定义函数。Power Query里可以使用文本函数按字符拆分再用M语言判断中文范围。思路是把文本转成列表遍历每个字符的Unicode编码筛选出需要的部分最后合并回去。Power Query的好处是数据清洗过程全程可视化刷新一遍就能自动重跑全表。VBA方案则更加直接。按AltF11打开编辑器插入一个模块写一个几行的Function即可Function SplitCN(ByVal s As String) As String Dim i As Long Dim result As String For i 1 To Len(s) If AscW(Mid(s, i, 1)) 127 Then result result Mid(s, i, 1) End If Next SplitCN result End Function保存后在单元格里输入SplitCN(A2)你就能得到一个提取完中文的自定义函数。VBA的好处是逻辑明确、运行速度快而且你可以随意扩展规则比如只保留汉字、过滤全角标点、判断拼音首字母。如果你有精力维护这是我最推荐的长期方案。3. 实战演练姓名提取与姓氏统计的完整流程3.1 场景一从“姓名拼音手机号”的混合格式中提取中文姓名假设A列数据长这样A列原始数据张伟ZhangWei 13800138000LiLei李雷 13912345678韩梅梅Han Meimei 13700001111Tom 汤姆 13611112222目标是从中提取出纯中文姓名。用第2章的方法二在B2输入TEXTJOIN(,TRUE,IF(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))127,MID(A2,ROW(INDIRECT(1:LEN(A2))),1),))向下填充后B列得到张伟、李雷、韩梅梅、汤姆。注意第4行的“Tom 汤姆”英文Tom被跳过空格被跳过剩下的中文姓名被完整保留。这类清洗逻辑在处理外籍员工名单、英文备注混录等场景中非常顶用。3.2 场景二中文姓名和英文姓名都在同一单元格时双向提取有些通讯录为了排版方便会把名字写成“Zhang Wei张伟”或者“王芳 Wang Fang”这种格式。这时单纯提取中文得到的是“张伟”或“王芳”提取英文得到的是“Zhang Wei”或“Wang Fang”。把两条公式并列写在两列就可以把一个单元格拆成“中文名”和“英文名”两个字段。这里有一个容易被忽略的坑如果原格式里包含“张伟”这种全角括号括号字符的CODE返回值同样大于127提中文时会把“”和“”一起带出来。所以提取完以后最好再用“查找替换”或者公式清理掉常见全角标点。也可以用SUBSTITUTE函数预先替换SUBSTITUTE(SUBSTITUTE(A2,,),,)这种处理看似不起眼实际在清洗真实数据时非常有价值。很多Excel用户第一次用分离公式后发现结果里带着各种奇怪的符号不是因为公式错了而是没提前清理全角标点。3.3 场景三姓氏统计——姓张、姓李、姓王各有多少人把中文姓名提取出来之后姓氏统计就是水到渠成的事。先新增一列姓氏列假设B列是提取好的中文姓名在C2输入LEFT(B2,1)这样就拿到了姓氏。注意复姓的情况比如“欧阳娜娜”用LEFT只能拿到“欧”。如果团队里确定有复姓需要用IF配合数组先判断前两个字是否为常见复姓逻辑会更复杂。我处理通讯录时一般会单独维护一份“复姓表”然后这样写IF(ISNUMBER(MATCH(LEFT(B2,2),复姓表,0)),LEFT(B2,2),LEFT(B2,1))这份复姓表只需要把“欧阳、司马、上官、诸葛、夏侯、东方、独孤、令狐”等常见复姓列出来即可。拿到姓氏之后统计某姓出现次数最直接的方式是COUNTIFCOUNTIF(C:C,张*)因为在COUNTIF里张*表示以“张”开头的单元格。这里统计的是“以张开头的姓名人数”。如果你想把所有姓氏的出现次数一次性排出来更方便的是插入数据透视表把“姓氏”列拖到行区域再把“姓名”列拖到值区域几秒钟就能看到全团队的姓氏分布。数据透视表在处理几千人的花名册时性能和灵活性远超公式。3.4 场景四给中文备注和英文备注自动打标签姓名统计之外CODE函数还能做一件很实用的事判断一个单元格到底是以中文为主还是以英文为主。比如B列是客户备注混合着“已成交”“VIP客户”“Need follow up”“催款中请重视”这类内容。你可以用中文字符占比来判断这条备注属于中文备注还是英文备注。计算中文字符数的数组公式SUMPRODUCT(--(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))127))这个公式返回A2单元格里中文字符的数量。再配合LEN(A2)总字符数就能得到中文占比。超过50%标记为“中文备注”否则标记为“英文备注”。我曾经用类似方法清洗过一批国际客户的跟进记录把几千条备注自动分成中英文两组后续分配给人处理时效率提升非常直观。4. 避坑指南CODE函数必须知道的6个常见问题4.1 为什么我提取出来的中文带着奇怪符号最普遍的原因是原文本中有全角标点、全角括号、全角空格。这些字符的CODE返回值大于127因此被当成“中文”保留了下来。解决方法有两种一是提前用SUBSTITUTE清理全角符号二是用UNICODE函数做更精确的判断。Excel 2013及以上版本提供了UNICODE函数它返回的是标准Unicode代码点而不是系统代码页的编码值。真正的汉字Unicode代码点大致落在19968到40959之间。用这个区间判断汉字比“CODE大于127”精确得多IF(AND(UNICODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))19968,UNICODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))40959),MID(A2,ROW(INDIRECT(1:LEN(A2))),1),)这段公式配合TEXTJOIN就能只提取真正的汉字剔除全角标点和全角字母。4.2 数组公式为什么显示#VALUE!错误或者结果为空绝大多数情况是忘了按CtrlShiftEnter。在Excel 2019及更早版本中这种包含数组运算的公式必须用三键确认。如果确认成功公式栏中会出现花括号。如果你用的是Excel 365动态数组自动溢出不需要三键但前提是你的软件已经支持动态数组引擎。还有一个容易踩的坑如果你的文本中根本没有任何中文字符那么TEXTJOIN返回的就是空字符串看起来像是公式坏了。这其实是正常结果不是错误。4.3 CODE返回负数是怎么回事在个别环境中特别是VBA代码里面中文字符的编码可能被当作带符号16位整数处理导致返回值变成负数。比如某个汉字编码的无符号数值是54992超过32767在VBA的Integer类型中就会溢出成负数。解决办法有两种判断时不要只看是否大于127而是同时判断“大于127或小于0”或者直接改用ASCW、UNICODE这类返回Unicode代码的函数避开代码页转换导致的正负号问题。4.4 ROW($1:$99)的固定范围导致漏字符很多网上的教程喜欢写ROW($1:$99)因为99个字符对大部分文本够用。但问题在于你的数据里如果恰好有超长文本超过99个字符的部分被静默丢掉了而且公式不会报任何错。排查起来特别隐蔽。我建议一律用ROW(INDIRECT(1:LEN(A2)))让序号范围跟随实际文本长度动态变化。虽然INDIRECT是易失性函数但只要数据量不是几十万行性能影响可以忽略。4.5 公式在WPS里不一样WPS表格的TEXTJOIN和数组公式支持情况比Excel稍复杂。早期版本的WPS需要三键确认新版WPS则逐步跟进了动态数组。如果你在WPS里使用上述公式遇到问题建议先确认WPS的版本是否支持TEXTJOIN如果版本较老可以用CONCAT函数代替TEXTJOIN但CONCAT不支持分隔符参数合并时会把所有内容直接黏在一起。老版本的替代公式可以写成CONCAT(IF(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))127,MID(A2,ROW(INDIRECT(1:LEN(A2))),1),))此公式同样需要三键确认。4.6 文本里包含换行符时会被误判为英文如果你的单元格里来换行符CHAR(10)它的CODE值是10小于127会被当成“英文”提取到英文部分。这倒不一定会让结果出错但会让英文部分看起来多了一些看不见的空行。如果要去掉换行符可以在提取之前用SUBSTITUTE清理SUBSTITUTE(A2,CHAR(10),)处理从网页或PDF复制过来的文本时这一步几乎是必备操作。5. 升级思路CODE函数还能怎么玩5.1 用字符代码做数据质量体检字符代码的应用绝不是中英文分离一个场景。你可以用类似的思路检测单元格里是否包含非法字符比如识别一串编号中间的隐蔽空格、判断手机号列有没有误录入字母、找出混在数字里的中文单位。举个例子检查A2单元格是否全部由数字组成可以用SUMPRODUCT(--(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))48))SUMPRODUCT(--(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))57))0当所有字符的代码都在48到57之间时说明A2全部是数字。这套逻辑比ISNUMBER(A2)更灵活因为它只针对文本内容本身做判断不受单元格格式影响。5.2 结合LENB和WIDTH函数处理双字节字符在Excel的旧式函数族中LENB按字节统计长度。中文字符占2个字节英文占1个字节。所以LENB(A2)-LEN(A2)的差值恰好等于中文字符数量。这是另一个经典的统计中文字符数的方法不需要数组公式计算速度更快。我经常用这个差值公式来快速核对CODE数组公式的结果。两者互相印证一旦出现不一致八成是文本里混入了全角符号或换行符。5.3 做成你自己的“一键清洗模板”如果你所在团队经常需要处理中英混合名单建议把文中提到的分离公式沉淀成一个Excel模板放在公共盘里。模板包括原始数据粘贴区中文名提取列英文名提取列姓氏提取列常见标点自动清理设置备注语言类型自动标记列这样的话同事拿到表格后只需要粘贴原始数据所有清洗结果自动更新不需要每个人都理解CODE函数的原理。根据我个人的经验把一次性操作固化成模板是Excel效率提升最明显的一步。因为在真实工作中最大的成本永远是来回沟通和反复手工清洗而不是那几行公式本身的运行时间。5.4 如果追求极致性能可以用Power Query或Python当数据量达到几十万行Excel的数组公式再高效也会变得卡顿。这时候建议把清洗逻辑迁移到Power Query导入数据后添加自定义列用M函数逐字符判断并合并。Power Query的好处是只在刷新时执行计算平时编辑操作不会拖慢Excel。如果你本身熟悉Python用pandas结合正则表达式处理这类中英文混合文本同样是顺手的选择数据的可维护性和逻辑清晰度都要优于Excel公式。不过这是另一个完整的话题了日常几百几千行数据用本文这套CODE方案已经完全够用。我在实际项目里的习惯是5000行以内优先用Excel公式5000行以上、数据源可能会频繁更新的一律走Power Query或者Python脚本。按这个原则来效率和团队的可使用性都能得到比较好的平衡。