做Excel数据整理的人大概都经历过这种崩溃时刻一张表里中英文姓名、中英混排的备注、带单位的文本全挤在一列想拆拆不开想统计统计不了。手动处理吧几百行数据能点到手抽筋上网找插件吧又要安装又要担心安全。其实Excel早就内置了一个不起眼却非常好用的函数——CODE它就是专治这种字符识别问题的利器配合LEFT、MID、SUMPRODUCT等函数中英文分离、姓名统计这些让人头疼的活儿几行公式就能搞定。这篇文章我想从一个实际使用者的角度把这个函数讲透它为什么能识别中英文、怎么用它做文本分离、怎么在姓名统计场景里发挥价值以及我用它处理数据时踩过的坑和总结出来的组合拳。不管你是Excel公式新手还是每天跟名单、报表死磕的老手这篇内容都能直接帮你省下大把时间。1. CODE函数的工作原理为什么它能一眼认出中英文1.1 字符编码每个字符的身份证号计算机里的所有字符不管是字母、数字、汉字还是标点符号在存储的时候都会被映射成一个数字编码。这个编码就像是每个字符的身份证号而CODE函数的作用就是返回文本字符串中第一个字符对应的编码。语法极其简单CODE(text)随手测几个常见字符你就明白了CODE(A)返回 65CODE(a)返回 97CODE(0)返回 48CODE( )返回 32CODE(!)返回 33反过来CHAR函数接收一个编码并返回对应字符CHAR(65)返回 A。这俩函数互为逆运算很多文本处理场景都需要配合使用。为什么这个函数值得专门写一篇文章因为CODE的输出值能直接反映出字符所属的族群。标准ASCII字符英文字母、数字、英文标点的编码都集中在0到127这个区间而中文字符在简体中文Windows环境下走的是GBK/GB2312双字节字符集编码值的范围完全不同。也就是说只要拿到一个字符的CODE值你就能判断出它到底是中文阵营还是英文阵营。1.2 中文环境下CODE返回值的诡异现象我第一次用CODE函数的时候被一个现象惊到了在中文系统上CODE(中)返回的不是什么200多或者300多的正整数而是一个负数。一开始我以为是公式写错了查了半天才明白原因。这是因为Excel在双字节字符集DBCS环境中会把两个字节拼合出来的编码当作16位带符号整数来解释。中文汉字的编码字节中最高位通常是1于是整个数值就被解释成了负数。下面是我在简体中文版Excel里实测的一组示意值不同系统版本可能有差异但规律是稳定的字符CODE返回值简体中文环境示意说明A65英文大写字母属于ASCII区间z122英文小写字母属于ASCII区间048数字属于ASCII区间半角空格32单字节空格全角空格负数全角符号已超出单字节区间中负数中文字符绝对值远大于127记住里面最关键的一条规律就行只要这个字符是中文或者全角标点CODE返回值的绝对值通常大于127只要是标准的ASCII字符英文、数字、半角标点绝对值一定在0到127之间。这条规律就是后面所有分离公式和统计公式的基石。1.3 判定中文开头的核心公式有了上面的规律判断一个单元格内容是不是中文开头一行公式就解决了IF(ABS(CODE(LEFT(A2,1)))127,中文开头,英文/数字开头)拆开看每一步的逻辑LEFT(A2,1)取出第一个字符。CODE函数只读取传入文本的首字符这里用LEFT包裹逻辑上更清晰也方便后续替换成MID或者RIGHT做灵活处理。CODE(...)拿到这个字符的编码。ABS(...)把可能出现的负数转成正数统一比较口径。这一步是灵魂后面踩坑章节会细说。127判断是否属于中文字符区间。这个公式就是整个CODE函数应用体系里的地基。后面的中英文分离、姓名统计本质上都是在它的基础上做扩展。你先把它写在Excel里拿张三John12345分别试一遍马上就能体会到字符识别四个字的分量。2. 中英文分离实战三个公式覆盖九成混排场景2.1 场景一给混合名单批量打标签假设你手里有一列客户名单中英文混在一起需要批量加上中文姓名或英文姓名的标记。A列数据长这样姓名目标结果张三中文姓名John Smith英文姓名李四中文姓名Alice英文姓名在B2输入核心公式然后往下拖IF(ABS(CODE(LEFT(A2,1)))127,中文姓名,英文姓名)这个操作本身不复杂但它解决的是一个高频痛点很多人遇到混合名单的第一反应是排序、筛选或者人工肉眼分辨费时费力。用CODE判定首字符等于让Excel替你做这一步眼力活。如果你还想更细地判断英文名是不是标准的大写字母开头可以再加一层IF(AND(CODE(A2)65,CODE(A2)90),大写字母开头,其他)这里直接对CODE(A2)做区间判断即可因为大写字母的编码就是65到90不涉及负数不需要加ABS。2.2 场景二两段式混排文本的拆分接下来是更硬核的场景一个单元格里既有中文又有英文或数字比如张三abc备注2024测试这种。先说结论如果数据是中文在前、英文/数字在后的两段式结构最稳的办法是配合LEN和LENB这对字节兄弟而不是单独用CODE逐字扫描。中文字符在数据库里占2个字节英文字符占1个字节于是就有两个经典公式中文字符数 LENB(A2) - LEN(A2)英文字符数 2 * LEN(A2) - LENB(A2)拿张三abc举例LEN(张三abc)返回5因为一共5个字符。LENB(张三abc)返回7因为张三各占2字节a、b、c各占1字节。所以前面中文部分长度 7 - 5 2后面英文部分长度 2×5 - 7 3。于是提取中文用LEFT提取英文用RIGHTLEFT(A2, LENB(A2) - LEN(A2)) 返回张三 RIGHT(A2, 2*LEN(A2) - LENB(A2)) 返回abc有人会问这篇不是在讲CODE吗怎么这里用LEN和LENB我的理解是CODE在这里扮演裁判员LENB扮演测量员。对单纯的前中后英结构LENB测量法最快最准但如果数据里混了全角标点、特殊符号LENB的字节计数就会被干扰这时候CODE逐字符扫描反而更可靠。两者不是替代关系是互补关系。2.3 场景三定位第一个英文字符的位置如果数据不是简单的中文在一段而是中文英文数字交错排列比如abc124号码007这种上面LENB的办法就失效了因为没有办法用一个固定的切分点分开。这时候CODE的逐字符扫描能力就派上用场了。思路很直白把文本拆成单个字符逐个计算CODE值找到第一个绝对值小于128的字符位置这个位置就是第一个非中文字符出现的位置。数组公式如下MIN(IF(ABS(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1)))128,ROW(INDIRECT(1:LEN(A2)))))拆解一下ROW(INDIRECT(1:LEN(A2)))生成一个从1到文本长度的序列。比如文本长度是9这里就生成{1;2;3;4;5;6;7;8;9}。MID(A2,这个序列,1)把每个字符逐个切出来。CODE(...)取每个字符的编码。ABS(...)128判断是否是ASCII字符是则保留该位置序号。MIN取最小的位置也就是第一个非中文出现的位置。拿到位置后想提取第一个非中文字符之前的中文部分就在外层套一个LEFTLEFT(A2, MIN(IF(ABS(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1)))128,ROW(INDIRECT(1:LEN(A2))))) - 1)如果你是Excel 365用户这样的数组公式直接回车就能出结果但Office 2019及更早版本必须按CtrlShiftEnter三键确认否则公式不会按数组方式运算结果一定是错的。这也是很多人公式下拉失效的常见原因之一——不是公式坏是没按三键。如果你手里的Excel是2019或365还可以用TEXTJOIN把所有中文字符一次抽出来TEXTJOIN(,TRUE,IF(ABS(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1)))127,MID(A2,ROW(INDIRECT(1:LEN(A2))),1),))这个公式的本质是把每个中文字符筛选出来再用TEXTJOIN拼回去算是CODE函数的一种盗火级用法。要注意TEXTJOIN在2016及更早版本里不存在用了会报#NAME?错误。2.4 整列落地兜底处理与下拉注意事项公式在单条数据上测试通过后整列应用一般就是直接下拉。但实战中我强烈建议多做一个兜底处理。比如用LENB拆两段式文本时如果单元格本来就是纯英文LENB(A2)-LEN(A2)算出来是0LEFT取0个字符会返回空值这倒还好但如果单元格是空的整套公式都会变成#VALUE!错误。我常用的兜底写法是IF(LENB(A2)-LEN(A2)0, A2, LEFT(A2,LENB(A2)-LEN(A2)))意思是如果中文长度为0就返回原文本否则返回中文部分。这样纯英文的记录不会被清空空单元格也不会一路报错传染下去。整列处理完再用TRIM(A2)清理首尾空格数据就基本干净了。3. 姓名统计进阶从分得出中英文到算得清姓氏分布3.1 用SUMPRODUCT一键统计中英文人数单条数据打标签好办但老板经常问的是这批名单里中文名有多少个、英文名有多少个。如果你先逐行拉公式再用COUNTIF数标签效率太低而且中间多出一列辅助数据后面清理起来也麻烦。直接用SUMPRODUCT和CODE组合一个公式出结果SUMPRODUCT(--(ABS(CODE(LEFT(A2:A100,1)))127))这个公式返回中文姓名的个数。英文姓名个数用同样套路把大于号换成小于SUMPRODUCT(--(ABS(CODE(LEFT(A2:A100,1)))128))为什么能这样写因为LEFT(A2:A100,1)会把整列首字符批量取出来CODE再批量转编码ABS和比较运算生成一组TRUE/FALSE逻辑值双减号--强制把它们转成1/0最后SUMPRODUCT求和就是计数。整个过程不依赖辅助列一次成型。有一点必须提醒如果区域里有空单元格CODE()会直接报错。稳妥的做法是加一个排除空值的条件SUMPRODUCT((A2:A100)*(ABS(CODE(LEFT(A2:A100,1)))127))另外区域尽量用绝对引用写死不要写A:A整列引用。整列计算会让SUMPRODUCT在几十万行数据上做无意义扫描卡到Excel直接转圈圈。3.2 拆解中文姓名姓与名的自动切分中文姓名的结构一般是姓单名或姓双名比如王芳王小明。如果要做进一步的姓名分析第一步就是把姓和名拆到两个独立字段。取姓公式LEFT(A2,1)取名公式MID(A2,2,LEN(A2)-1)这里最巧妙的是LEN(A2)-1。它自动适配名字长度王芳LEN2MID从第2位取1位得到芳。王小明LEN3MID从第2位取2位得到小明。如果遇到复姓欧阳、司马、诸葛单字的取姓公式就不够用了。思路是维护一个复姓清单先判断前两个字是否在复姓表里IF(COUNTIF(复姓表,LEFT(A2,2)),LEFT(A2,2),LEFT(A2,1))COUNTIF函数会返回1或0配合IF直接判断。现实中复姓名单往往只有几十个维护成本很低但能覆盖大多数边界情况。3.3 姓氏频次统计排行榜这样生成统计每个姓氏的人数是人事、行政、销售场景里的经典需求。做法很简单先把姓提取到辅助列B列然后去重再逐个COUNTIF。核心公式LEFT(A2,1)去重后统计某个姓氏王有多少人COUNTIF(B:B,王)不想用辅助列可以直接对原始姓名列做通配符统计COUNTIF(A:A,王*)通配符*代表任意多个字符王*的意思就是以王开头的所有单元格。这个写法在处理张三、王五、王小二这类数据时非常好用也是生成姓氏排行榜的基础。英文姓名的情况不一样老外习惯名在前、姓在后中间用空格隔开。要提取英文姓先用FIND定位空格位置FIND( ,A2)然后英文名空格前的部分LEFT(A2,FIND( ,A2)-1)英文姓空格后的部分RIGHT(A2,LEN(A2)-FIND( ,A2))FIND是姓名统计里特别重要的辅助函数它和CODE一起基本可以覆盖中文姓名和英文姓名两套完全不同的统计口径。3.4 中英文姓名混合表的统一统计模板实际工作中一张表里中英文姓名混排是最常见的情况。我的做法是做一个统一的处理模板把姓名类型、姓、名分别拆到独立列后面用数据透视表还是COUNTIF都随你。B列判断姓名类型IF(ABS(CODE(LEFT(A2,1)))127,中文,英文)C列取姓中文取首字英文取空格后的部分IF(B2中文,LEFT(A2,1),IF(ISNUMBER(FIND( ,A2)),RIGHT(A2,LEN(A2)-FIND( ,A2)),))D列取名中文取剩余部分英文取空格前的部分IF(B2中文,MID(A2,2,LEN(A2)-1),IF(ISNUMBER(FIND( ,A2)),LEFT(A2,FIND( ,A2)-1),A2))这三列做完你会发现所有的统计需求都变得异常简单中文姓氏TOP10对C列做COUNTIF排序。英文姓名重复率对D列做分类汇总之类的操作。中英文比例对B列做透视表。我之前处理一份两千多人的活动报名表就是靠这个模板十分钟拆完然后透视表出所有统计口径。如果没有这套拆列光是区分中英文姓名就得折腾半天。4. CODE函数踩坑实录六个细节让结果差之千里4.1 只认第一个字符不认整串文本CODE函数有一个非常容易让人误会的设定它只返回第一个字符的编码。CODE(中国)返回的不是中和国两个字的编码而是中这一个字的编码。如果你想检查文本中每一个字符就必须借助MID配合ROW(INDIRECT(...))序列逐个取字符再用SUMPRODUCT或者数组公式汇总。很多人第一次写公式报错或结果不对八成是没搞清楚这个首字符限定。4.2 负数陷阱比较前必须加ABS这是CODE函数新手最容易踩的坑。直接写CODE(中)127结果返回FALSE因为中的编码是个负数负数当然不大于127。你必须在外面套一层ABS把它转成正数再跟127比较。可以这么说所有涉及中文判定的CODE公式ABS基本是标配。少了ABS公式看起来逻辑没错但运行起来全是反的而且极难排查。4.3 全角、半角字符会冒充中文全角字母、全角数字、全角标点在存储时也是双字节的编码绝对值同样大于127。也就是说ABS(CODE(LEFT(,1)))127的结果是TRUE尽管本质上是英文字母的全角写法。如果你的数据经常从网页或者其他系统粘贴过来很容易混进全角字符导致统计口径失真。处理办法是先做规范化把全角转半角可以用SUBSTITUTE逐字符替换或者在网上找一段全角半角转换的宏代码。最省事的办法是在公式里加一个前置处理比如先TRIM(A2)再去判断。4.4 不可见字符带来的假英文从网页、数据库导出的文本经常自带换行符、制表符等不可见字符。CODE碰上这些字符返回的可能是10、9这类很小的ASCII值于是明明是一个中文单元格首字符却因为藏着换行符被判成英文开头。排查方法很简单用CODE(LEFT(A2,1))看看实际返回值如果是个两位数的小数字多半就是不可见字符。处理时对原始数据先做一次CLEAN(A2)把非打印字符清掉再做后续判断。4.5 系统区域设置影响返回结果CODE函数的返回结果和系统当前的区域设置、字符集是绑定的。在简体中文系统上中文字符返回的是负值或大于127的值但同样的公式换到英文系统上中文可能直接被映射成问号等替代字符返回63整套绝对值大于127判中文的逻辑就失灵了。如果你的Excel文件要跨区域、跨版本共享严谨的做法是不依赖单一CODE返回值而是用UNICODE函数配合固定判断区间或者提前在不同环境里做一轮验证。4.6 新版本Excel用UNICODE函数更规整Excel 2013及以上版本提供了UNICODE函数它直接返回字符的Unicode码点比如UNICODE(中)返回20013。这是全球统一的编码标准不随系统区域设置变化。用它做中文判定更干净IF(UNICODE(LEFT(A2,1))255,中文,英文)ASCII字符最大127扩展拉丁字符最大255而汉字的Unicode码点从19968开始用255做分界线非常清晰。唯一的问题是老版本Excel2010及之前没有UNICODE函数如果你主要在老旧环境工作还是得老老实实用CODE加ABS的组合。5. 组合拳扩展CODE还能顺手解决这些Excel难题5.1 快速识别单元格里是否混入了中文除了首字符识别CODE还能判断整个单元格是否包含中文。方法是用SUMPRODUCT逐字符扫描只要有任意一个字符的CODE绝对值大于127就判定包含中文IF(SUMPRODUCT(--(ABS(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1)))127))0,含中文,不含中文)这个公式在数据清洗场景里非常有用。比如你有一批产品名要求必须用英文拿这个公式一扫混入中文的单元格全部自动标记比肉眼检查快几个量级。老版本Excel记得三键确认365版本直接回车。5.2 检查编号是否以数字、大写字母开头校验编号、工号、订单号格式时经常要判断首字符的类型。数字的ASCII码是48到57大写字母是65到90小写字母是97到122。三个区间一组合公式非常直观IF(AND(CODE(A2)48,CODE(A2)57),数字开头,非数字开头)想识别以大写字母开头IF(AND(CODE(A2)65,CODE(A2)90),大写字母开头,其他)这类写法本质上就是把CODE函数当成字符类型探测器而不仅仅是查编码的工具。很多数据校验需求都能用这个思路秒掉。5.3 用CHAR反向生成字符序列既然CODE能取编码CHAR就能反推字符。这个能力在批量生成测试数据、构造字符表时特别好用。比如在单元格里生成A到ZCHAR(ROW(65:90))如果是365的动态数组这个公式会直接溢出26个字母老版本则需要选中纵向的26个单元格输入公式后按CtrlShiftEnter。同理小写字母用CHAR(ROW(97:122))数字0到9用CHAR(ROW(48:57))。我经常用这个技巧生成一个大小写字母数字的全量字符表然后配合CODE公式反查各种编码规则两边对照定位问题特别快。5.4 与其它方案怎么选一张表看清楚做了这么多年数据处理我觉得有必要把几种常见的中英文分离方案放在一起对比方便你按自己的环境选方案核心函数优点缺点适用场景编码判断法CODE/UNICODE兼容性好可逐字符判断全角字符会误判老版本要注意负数分类打标、边界定位、格式校验字节测量法LENB/LEN公式短算两段式数据极快中英交错或含全角字符时失效中文在前、英文在后的规则文本TEXTJOIN抽取法TEXTJOINMIDCODE一次抽出所有中文字符需要Excel 2019及以上提取纯中文部分正则函数法REGEXTEXT等功能最强能处理复杂交错的文本只有Excel 365新版本支持复杂混排、批量替换Power QueryM语言/界面操作可视化适合重复性数据清洗需要另起一个查询步骤学习成本稍高固定流程的清洗任务我个人现在的习惯是先看数据结构和版本环境。如果只是给名单打标签CODE加ABS三秒搞定如果要做复杂的中英交错提取直接上Power Query不在公式里硬杠。CODE函数真正的价值在于它足够轻、足够通用在90%的日常场景里它都是最快的解法而且它教给你的是用编码思维看文本这个底层能力这个能力迁移到任何数据处理工具里都不过时。回到文章开头那个场景——中英文混合的名单、拆不开的备注、理不清的姓名结构。你现在应该有了清晰的思路判定首字符用CODE加ABS两段式拆分用LENB加LEN分类统计用SUMPRODUCT姓名结构拆解用LEFT加MID加FIND。这套组合拳不依赖任何插件原生Excel就能跑学一次用十年下次再遇到类似的数据直接抄作业就行。