开头先说个现象。我在帮朋友处理一张几百人的报名名单时发现问题远比想象中麻烦同一列里既有“张三138****abcd”这种带备注的又有“Johnson 王”这种中英混排的还有全角空格、乱码、甚至日期被Excel自动转成了五位数序列号。手动一个个清理能把自己逼疯。后来我意识到几乎所有文本清洗问题都绕不开一个容易被忽视的基础函数——CODE。配合它做中英文分离、姓名统计、脏数据识别基本能做到“一条公式解决一类问题”。这篇东西不是讲函数手册里的干巴定义而是把我实际用CODE函数做中英文分离、姓名统计、名单清洗的经验全部写出来。包括公式怎么写、为什么这么写、在哪些版本上能跑、哪些场景容易翻车。适合正在跟Excel原始数据搏斗的运营、人事、行政、财务以及一切定期处理“人肉输入表格”的朋友。1. CODE函数的核心原理字符也有“身份证号”1.1 CODE函数到底返回什么很多人第一次接触CODE函数是听说“它能返回字符的编码”。这话对但不完整。CODE(文本)返回的是文本中第一个字符对应的数字编码在Windows版的新版Excel里这个编码就是该字符在Unicode字符集中的码点。比如说CODE(A)返回65大写字母A的Unicode码点就是65CODE(a)返回97小写字母a对应97CODE(中)返回20013对应汉字“中”的Unicode码点U4E2DCODE(1)返回49你可以在单元格里逐个试试。理解了这一点CODE函数就不是一个“只能转编码”的冷门函数而是一个“字符分类器”——它能把不可见的字符类型差异变成可比较的数值区间。1.2 中英文在编码层面差在哪中英文分离之所以能靠CODE实现核心原因是两类字符的码点区间截然不同几乎不重叠字符类型Unicode码点范围十进制范围典型例子半角数字U0030 ~ U003948 ~ 570-9半角大写字母U0041 ~ U005A65 ~ 90A-Z半角小写字母U0061 ~ U007A97 ~ 122a-z中文常用汉字U4E00 ~ U9FFF19968 ~ 40959中、文、分、离全角字母数字UFF01 ~ UFF5E65281 ~ 65374、、注意最后一行这是很多人踩坑的地方全角英文字母“”不是半角A的码点是65313跟半角A差了65000多。用CODE函数一眼就能看出某个“看着像A”的字符其实不是标准ASCII字符。1.3 为什么不能只用CODE一个函数直接分列这里要澄清一个误区CODE函数一次只能处理一个字符串的第一个字符它本身不具备“遍历字符串每个字符”的能力。比如你要判断“abc中def”这个字符串里有没有中文直接CODE(A1)只能看到字母a的65根本看不到后面的“中”。所以实战中CODE函数必须跟MID、RIGHT、LEFT、ROW、INDIRECT、SUMPRODUCT、TEXTJOIN这些函数组合才能实现“逐字符扫描”的效果。我把CODE当成“字符探测器”它配合MID这个“抓手”逐字取样再用IF做“分类筛选”最后用TEXTJOIN或辅助列把结果拼回来。这套打法是本文的核心框架。2. 三种主流的中英文分离方案按上手难度排序2.1 方案一LEN与LENB的字节差最快最稳如果你只需要把“中英文分居两侧”的字符串拆开比如“张三zhangsan”这种左侧中文、右侧字母的用LEN和LENB就够了甚至不需要CODE。先讲原理。LEN函数统计字符个数LENB函数统计字节数。中文字符在字节层面占2个字节半角字母数字占1个字节。所以LEN(张三abc)返回5因为一共5个字符LENB(张三abc)返回7因为两个汉字算4字节三个字母算3字节两者之差LENB-LEN2正好等于中文字符的个数知道中文有2个之后配合RIGHT和LEFT就能拆分了。假设A2是“张三zhangsan”提取中文RIGHT(A2, LENB(A2)-LEN(A2))结果“张三”提取英文LEFT(A2, LEN(A2)-(LENB(A2)-LEN(A2)))结果“zhangsan”这个方案是“性价比之王”新手也能秒懂。但有一个前提文本里的中文必须集中在一侧且除了中文和半角字符外没有全角字母、全角标点。如果遇到“ab陈c”这种中英交错这个方案就会计算出错因为你拿到的“中文个数”只能从末尾截。2.2 方案二CODEMID数组公式逐字识别更灵活遇到中英文交错、夹数字、夹特殊符号的情况就要请出CODE函数了。思路也很直白用MID把字符串里的每一个字符都“掏”出来再用CODE判断每个字符落在哪个区间最后把符合条件的那几个字符重新拼起来。在Excel 365或Excel 2021里可以用TEXTJOIN配合数组运算公式非常简洁。以A2为“ab陈c12测试”为例提取所有中文TEXTJOIN(, TRUE, IF((CODE(MID(A2, ROW(INDIRECT(1:LEN(A2))), 1))19968)*(CODE(MID(A2, ROW(INDIRECT(1:LEN(A2))), 1))40959), MID(A2, ROW(INDIRECT(1:LEN(A2))), 1), ))这个公式看着吓人拆开看其实就三件事MID(A2, ROW(INDIRECT(1:LEN(A2))), 1)从第1个字符取到最后一个字符生成一个字符数组CODE(…)把每个字符转成数值IF(判断值19968 且 40959, 返回该字符, 返回空)只要码点在汉字区间就保留否则丢弃TEXTJOIN的第二个参数TRUE表示忽略空值所以最后拼出来的就是一段连续中文。如果你要提取英文和数字把区间判断改成三组条件相加即可。这里要提醒一句老版Excel没有TEXTJOIN数组公式也要求按CtrlShiftEnter输入。老版本用户可以把同样的逻辑拆到辅助列里B2单元格写MID($A2, ROW()-1, 1)C2写IF(AND(CODE(B2)19968,CODE(B2)40959), B2, “”)然后下拉最后再用CONCATENATE拼接非空值。方法笨一点效果完全一致。2.3 方案三Power Query分列适合批量表格如果你的中英文分离任务不是一次性的而是每个月都要处理同类表格我建议你直接把数据丢进Power Query写一个自定义列来完成分离。Power Query里判断中文的逻辑跟CODE不一样它用的是ByteCharacter类型判断但同样基于Unicode码点区间let 源 Excel.CurrentWorkbook(){[Name表1]}[Content], 已添加自定义 Table.AddColumn(源, 中文部分, each Text.Select([原始文本], {一..鿿})) in 已添加自定义Text.Select函数可以按字符区间过滤把中文字符全部提取出来英文数字则可以用Text.Remove清除掉。这个方案的优点是不用写长公式处理上万行数据也不卡缺点是“不是每个人都会打开Power Query”如果你要交付的表格是给不熟练的同事用的最终可能还是要转换成公式方案。我的建议是一次性处理用LEN/LENB或CODE的公式方案每月固定流程用Power Query方案如果单位用的是WPS某些函数支持度不同也要先测试再做决定。2.4 三种方案怎么选场景推荐方案理由中英文分别在两侧结构规整LEN/LENB字节差法公式最短不依赖版本中英文交错或需要精确提取数字/字母CODEMIDTEXTJOIN数组法灵活可按码点区间自定义每月大批量处理流程固定Power Query可复用性能好老版本Excel2016及以前辅助列拆分法不需要新函数兼容性强3. 姓名统计实战从脏乱名单到干净数据3.1 场景一检测姓名里混入的字母、数字和全角空格做人事和行政的人应该都懂员工填写的“姓名”列永远是重灾区。有人填“李四(13212345678)”有人填“王五 wang”有人填“”甚至有人姓名中间夹着全角空格。这时候CODE函数就能派上用场了。先做一个“是否含非中文字符”的判断列。核心逻辑是把姓名逐字符拆出来检查是否有任意一个字符的码点不在汉字区间内。以A2为姓名在B2输入IF(SUMPRODUCT(--NOT((CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))19968)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))40959)))0, 有异常, 正常)这个公式的意思是只要字符串中存在任意一个字符的码点不在19968~40959之间就标记为“有异常”。配合条件格式把“有异常”标红数据问题一目了然。如果你只想检查姓名是否包含空格包括全角空格码点12288和半角空格码点32可以单独加一列IF(OR(CODE(A2)32, CODE(A2)12288), 含空格, 无空格)不过这个公式只能检查首字符是空格的情况。更严格的做法还是用上面的逐字符扫描公式只是把判断条件改成“是否存在码点等于32或12288的字符”。3.2 场景二单姓和复姓的分组统计姓名统计里有个很常见但很头疼的问题复姓。李、王这种单姓用LEFT(A2,1)提取就行但“欧阳”“司马”“上官”这种复姓如果只取第一个字统计结果就会碎掉。CODE函数本身不直接解决复姓识别问题但它能帮我们确认姓名字符的类型从而避免把英文名或拼音混进姓氏统计。比如我先用前面介绍的“是否含非中文字符”检查列清掉异常数据再用一个复姓清单来做分组。假设G列放复姓清单欧阳、司马、上官、诸葛等A列是完整姓名在B2写IF(COUNTIF($G$2:$G$20, LEFT(A2, 2))0, LEFT(A2, 2), LEFT(A2, 1))这样就可以批量生成“姓”列了。再配合COUNTIF就能统计出“哪个姓的人数最多”COUNTIF($B$2:$B$200, B2)这套组合拳的关键是先清洗再统计。我见过太多人直接拿原始姓名列做透视表结果“李四(123)”和“李四”被当成两个人。清洗这一步不要偷懒。3.3 场景三按姓氏和姓名长度汇总有时候领导要的不是“哪个姓人多”而是“名单里有没有重名的”“有没有超长姓名”。中文姓名一般在2~4个字如果出现了一个5个字的名字大概率是“李小明abc”这类脏数据。姓名长度判断不用CODE用LEN就够LEN(A2)。但结合CODE检测后你就能识别出“看起来是5个字实际里面有2个英文字母”的情况。做法是写一列“清洗后姓名字符数”SUMPRODUCT((CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))19968)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))40959))这个公式会统计出A2中真正汉字的个数。如果这个值小于LEN(A2)说明姓名里有非汉字混入。比如“李四abc”的LEN是5但汉字个数是2就能快速被识别。进一步你可以写个IF判断IF(LEN(A2)SUMPRODUCT((CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))19968)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))40959)), 纯姓名, 含非汉字)这样一份“姓名质量报告”就出来了。3.4 结合SUMPRODUCT做多条件统计除了清洗CODE还能帮我们统计某些区间字符的数量。比如领导要你统计名单里有多少人姓名包含“力”字旁的字或者有多少人姓名首字母是A~Z其实是填了英文名。这时候SUMPRODUCT和CODE的组合就非常好用。统计A列中首字母为大写英文字母的姓名数量SUMPRODUCT(--(CODE(LEFT(A2:A200,1))65)*(CODE(LEFT(A2:A200,1))90))注意老版Excel里LEFT(A2:A200,1)这种区域数组运算需要搭配SUMPRODUCT才能逐行计算。它会把每一行的首字符依次提取出来再判断码点是否落在65~90之间。这个技巧常用于快速识别“哪些报名者填了英文名”。4. 完整实操搭建一个可复用的姓名清洗模板4.1 表结构设计纸上谈兵聊完了我直接给你一套可以抄作业的表结构。这是我日常处理报名表、通讯录、成绩单时用的模板包含原始数据、清洗中间列和最终结果列列列名作用A原始姓名业务部门直接导出的脏数据B是否含非汉字快速筛选异常记录C汉字个数用于判断姓名长度是否异常D提取出的中文清洗后的干净姓名E提取出的英文数字如果存在则单独展示F姓氏按单姓/复姓规则提取G重名次数统计同姓名出现次数4.2 核心公式组合以第2行为例逐一写下公式B2判断是否含非汉字IF(SUMPRODUCT(--NOT((CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))19968)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))40959)))0, 含非汉字, 纯汉字)C2统计汉字个数SUMPRODUCT((CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))19968)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))40959))D2提取全部中文Excel 365可用老版本建议用辅助列TEXTJOIN(, TRUE, IF((CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))19968)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))40959), MID(A2,ROW(INDIRECT(1:LEN(A2))),1), ))E2提取英文和数字TEXTJOIN(, TRUE, IF((CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))48)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))57)(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))65)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))90)(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))97)*(CODE(MID(A2,ROW(INDIRECT(1:LEN(A2))),1))122), MID(A2,ROW(INDIRECT(1:LEN(A2))),1), ))F2提取姓氏IF(COUNTIF(复姓清单区域, LEFT(D2,2))0, LEFT(D2,2), LEFT(D2,1))注意这里要用D2而不是A2因为D2是已经清洗过的纯中文姓名不会受英文和括号干扰。G2统计重名次数IF(D2, , COUNTIF($D$2:$D$500, D2))4.3 自动验证与条件格式公式列出来后还要加两道“保险”。第一道是数据验证。在B列“是否含非汉字”这一列设置条件格式选中B2:B500公式规则填$B2含非汉字然后设置红色填充。这样哪些行有问题一眼就能扫出来。第二道是异常姓名拦截。可以在H列加一个“是否清洗”的判断当D2不等于A2时说明原始数据里混入了非汉字内容需要人工确认。这里可以写IF(A2D2, 请人工确认, OK)这一步的价值在于自动化不等于全自动涉及人员名单的场合最好保留一道人工确认环节避免把有效信息误删。4.4 更进一步的动态数组写法如果你的Excel版本支持动态数组365/2021公式可以写得更好看。把上面的逐行公式改成区域公式比如在D2输入TEXTJOIN(, TRUE, IF((CODE(MID(A2, SEQUENCE(LEN(A2)), 1))19968)*(CODE(MID(A2, SEQUENCE(LEN(A2)), 1))40959), MID(A2, SEQUENCE(LEN(A2)), 1), ))用SEQUENCE(LEN(A2))替代ROW(INDIRECT(...))公式会自动生成1到字符串长度的序列不用再考虑老版本“是否按CtrlShiftEnter”的问题。区域数组一次处理500行时动态数组方案的计算效率也比逐行公式高不少。不过我得提醒一句动态数组公式在协同编辑的Excel Online上有时会出现兼容问题如果你的团队多人同时编辑一份大型表格建议还是用传统公式加辅助列稳定优先。5. 常见问题与排查技巧实录5.1 CODE返回#VALUE!错误怎么办CODE报错最常见的原因是参数引用了一个空单元格或空文本。空文本的CODE会被判断为参数无效。解决方法是加IF判断IF(A2, , CODE(A2))另外CODE函数内部如果写成了CODE(A2:A10)这种区域形式在普通单元格里会返回#VALUE!因为CODE不支持区域参数逐行返回。要逐行判断就必须配合SUMPRODUCT或BYROW这类可以遍历数组的写法。5.2 生僻字和扩展汉字识别不到Unicode汉字主区间是U4E00到U9FFF覆盖了绝大部分常用字但生僻字和古汉字可能落在扩展B区U20000之后这部分码点已经超出了单字符CODE函数在BMP内的处理范围。如果你的名单里出现“”这种扩展字符CODE的区间判断就会失明。遇到这种情况我的建议是不要硬用CODE区间全覆盖判断改用LEN和LENB的字节差来辅助验证扩展汉字的LENB通常也是2虽然无法精确识别它是汉字还是其他符号但至少能区分中英文。或者直接升级到Power Query里用Text.Select按字符区间筛选Power Query对Unicode字符的处理比Excel的CODE函数宽容得多。5.3 粘贴失效、加载项被禁用、日期变五位数这些“Excel日常坑”说回我开篇提到的那些热词CtrlV失效、excel加载项被禁用、日期变成五位数字、文件报“格式或扩展名无效”。这几个问题虽然不是CODE函数引起的但在我做名单清洗时反复遇到过顺手分享下排查顺序。粘贴失效大概率不是Excel本身问题而是加载项冲突或剪贴板进程卡死。我常用的解决办法是先保存文件关闭所有Excel窗口重启再不行就在“开发工具”里查一下有没有第三方加载项例如某个PDF转换工具、OCR插件把可疑的勾选去除。重开之后通常就好了。如果还是不行检查一下是不是同时开着两个版本的Excel比如企业版加个人版多个COM加载项抢占剪贴板也会导致CtrlV失效。日期变成五位数这个坑也常和数据导入有关。比如你从数据库导出的身份证号、日期被Excel自动识别成数字序列号。清洗时如果名单里混着出生日期列就要先把单元格格式改成“文本”再重新粘贴。已经变了的可以用“分列向导”的“文本”格式恢复或者用TEXT函数转换。文件报“格式或扩展名无效”通常是网上下的.xlsx其实不是真正的xlsx可能是旧版xls改后缀名或者文件损坏。这个跟Excel版本兼容性有关我的习惯是收到外部表格后先点“另存为”转成当前默认格式再处理否则后面所有公式都可能算不出来。5.4 常见问题速查表问题原因快速处理CODE返回#VALUE!引用了空字符串或区域加IF判空或改用SUMPRODUCT一个字符都提取不出来字符串可能是全角状态输入码点区间不符检查是否全角用CODE查看实际码点中英文分离结果顺序错TEXTJOIN只按原字符顺序拼接没法自动重排先想好规则分别提取中文/英文不要混拼表格里日期变五位数单元格文本被自动转数字先设文本格式再用分列向导恢复CtrlV粘贴失灵COM加载项或剪贴板进程冲突重启Excel排查可疑加载项加载项被禁用安全策略限制在信任中心检查“禁用的项目”确认来源安全再启用LEN/LENB统计中文个数不对文本混入全角字母、全角标点先用CODE逐字符扫描修正再统计结尾一点实际体验与后续方向这套CODE函数的用法我前后在报名数据处理、客户名单清洗、题库文本分离这三个场景里反复验证过。最直观的感受是会写长公式不如会分类CODE函数真正的价值不是“算出编码”而是把杂乱无章的字符变成可分类、可筛选、可统计的数值区间。遇到任何文本清洗需求我都会先问一句这批字符的码点区间是什么后续如果你想继续往下玩可以在这个基础上加VBA自定义函数把“提取中文”封装成一个叫GetChinese的私有函数一步调用。也可以学会用Power Query做更复杂的正则式清洗。但在这之前先把手里的CODE公式练熟至少遇到“中文英文数字搅一团”的表格时不会再头皮发麻。