上周帮财务改一张打印用的对账单问题卡在“收件信息”那一列。以前同事都是手动AltEnter换行排版几十条地址一条条敲回车月底赶工时敲到手腕疼换个人改模板还会全乱。我接手后大概花了五分钟改成公式驱动CHAR函数负责换行TEXTJOIN负责动态拼接再配合自动换行格式。从那以后对账单一刷新地址自动分行填新数据不用碰格式。类似这样的场景CHAR函数在Excel里其实比你想象的更常用。如果你经常做报表模板、拼接地址、清洗从网页或PDF粘过来的脏数据或者想在单元格里插入对勾、叉号、引号一类的特殊符号这篇文章应该能帮你省下不少时间。1. 先搞清楚CHAR到底是什么一个数字对应一个字符1.1 字符集、码位与CHAR的工作方式计算机里其实没有“符号”只有数字。每个字符都对应一个数字编号这个编号叫码位。CHAR函数干的事情特别简单你给它一个数字它去查表然后把对应的字符给你返回回来。比如你写CHAR(65)结果就是大写字母A。写CHAR(97)结果是小写字母a。这套表在Windows系统里默认是ANSI字符集0到127这一段和标准ASCII一致包含英文字母、数字、常见英文标点和控制字符。128到255是扩展区根据系统代码页不同显示的内容会有差异。在简体中文系统上这段区域可以显示一些特殊字母、制表符和符号但跨系统就不一定稳定。这里有一个经常被忽略的事实Excel的CHAR函数官方文档说参数范围是1到255超过255会返回错误。所以你不能指望用CHAR(10003)生成对勾那种超出ANSI范围的字符得用UNICHAR函数。这个坑我在第1.3节详细说。1.2 真正值得背下来的常用码位CHAR函数的码位有很多但日常用得上的其实就那么十几个。我把常用的整理成一张表建议收藏用的时候直接查码位生成字符典型场景9制表符Tab生成TSV文件、对齐列数据10换行符LF单元格内自动换行、拼接多行文本13回车符CR处理从DOS/旧系统导入的脏数据32空格和TRIM配合清理空格数量34双引号在公式里构造带引号的字符串44逗号,拼接CSV、分隔列表65/97A / a生成英文字母序列160不间断空格清理从网页复制数据时产生的乱码空格这里面最常用的是10和34。10是单元格内软换行34是双引号。这两个我后面都会展开讲。1.3 CHAR和UNICHAR怎么分工别再把特殊符号搞乱很多人在网上搜“用CHAR函数输入特殊符号”然后照着代码试输入CHAR(10003)想得到对勾结果要么返回错误值#VALUE!要么出来一个完全不相干的字符。问题就出在混淆了CHAR和UNICHAR。CHAR函数的适用范围是ANSI字符集也就是0到255。遇到码位超过255的字符Excel专门提供了UNICHAR函数它返回的是Unicode码位对应的字符。比如UNICHAR(10004) 返回对勾✓ UNICHAR(10006) 返回叉号✖ UNICHAR(9733) 返回实心五角星★ UNICHAR(9734) 返回空心五角星☆简单记只要是码位超过255的符号一律用UNICHAR。而控制字符、ASCII字符和ANSI扩展区的字符用CHAR就够了。这个边界搞清楚能省掉很多摸不着头脑的错误。2. 用CHAR(10)把单元格变成“迷你排版区”自动换行的正确姿势2.1 两种产生换行路径手动快捷键和公式拼接在Excel里单元格内换行最常见的入门操作是AltEnter。这个是手动换行好处是所见即所得坏处是没法批量生成也没法跟随数据变化自动更新。公式拼接的思路完全不同用CHAR(10)作为换行符通过“与”符号把多段文本连接起来。比如A2 CHAR(10) B2 CHAR(10) C2这样得到的单元格内容在数据上是一串包含换行符的文本。只要源数据变了拼接结果自动变格式永远不用再手调。不过要提醒一句CHAR(10)只是把换行符写进了单元格内容里你还需要给单元格打开“自动换行”格式Excel才会真正把换行符显示成换行效果。否则你在编辑栏里能看到内容分了两行但单元格里看起来还是一坨。2.2 案例分析一张自动分行的收件信息卡片我帮财务改造对账单时做的就是这类事情。原始表长这样A列姓名B列电话C列邮箱D列地址张三13800138000zhangsanexample.com某市某区某路88号要在新表里生成一个“收件信息”列每行显示成四行文本F2CHAR(10)G2CHAR(10)H2CHAR(10)I2然后把“自动换行”打开设定列宽行高选择“自动调整”预览效果就是标准的四行信息卡片。这样做的好处有两个新增数据时公式自动生成不需要再手动敲换行。地址长度不一致也不怕只要列宽固定换行显示由Excel按字符数自动处理不会出现有的行空白、有的行挤爆的情况。之前财务同事的做法是复制粘贴地址之后在一个单元格里手动AltEnter分段每个月做一次就要重新折腾一遍。改用公式之后这部分工作完全消失。2.3 用TEXTJOIN动态拼接多行汇总替代手工搬运如果在单元格里需要展示一个“列表”而不是固定三段那就要配合条件判断了。比如你有一个部门人员名单表想在汇总单元格里把某个部门的所有人名列出来每行一个名字TEXTJOIN(CHAR(10),TRUE,IF($B$2:$B$100E2,$C$2:$C$100,))这是数组公式Office 365和Excel 2021里直接回车即可老版本需要按CtrlShiftEnter确认。公式的逻辑是在B列里找等于E2部门名称的单元格把对应的C列姓名收集起来用换行符连接TRUE表示忽略空值。实际效果是E2下拉选择不同的部门单元格里的人员名单自动变成一份换行显示的清单。过去要找人、复制、拼接的做法现在全是自动的。2.4 自动换行格式的两个隐藏坑合并单元格和打印截断用了CHAR(10)之后还有个很常见的坑合并单元格自动换行时Excel不会自动调整行高。你辛辛苦苦拼好了多行文本一旦合并了单元格行高可能不够结果打印出来最后一行被截掉。我自己的处理方式是尽量不用合并单元格做这种版式改用“跨列居中”。给单元格打开自动换行、对齐方式选居中然后把水平对齐里的“跨列居中”打开选中连续几个单元格作为展示区域效果和合并类似但行高能正常调整。另外一个跟打印相关的问题换行文本在屏幕上显示正常打印预览里发现有的行被切断。这种情况大概率不是CHAR(10)的问题而是行高没有设置成“自动调整”。选中目标行在行号上右键选择“最适合的行高”再进打印预览复核一遍。如果打印范围固定尽量把行高值设成能容纳最大字符数的固定值避免不同数据导致行高忽高忽低。3. 特殊符号的批量入场从对勾叉号到报表标记3.1 为什么很多报表场景不用输入法而是用函数生成符号做报表时经常需要在结果旁边加对勾、叉号、星号这类标记。很多人选择手动输入或者用条件格式。手动输入的问题是数据变化了标记不会跟着变每次都要重新打一遍。函数生成符号的不同在于它跟业务逻辑绑定。成绩是否合格、任务是否完成、库存是否低于阈值这些判断本身就有规则完全可以让公式自动输出对应的符号。3.2 用UNICHAR生成动态√/×标记的写法比如一张成绩表D列是分数要求60分及以上显示对勾否则显示叉号IF(D260,UNICHAR(10004),UNICHAR(10006))有人用IF(D260,√,×)也能实现效果差不多但直接用函数码位的好处是可以批量应用到大量行也方便后续用查找替换统一修改符号风格。比如财务的月度对账单里已核对显示对勾、未通过显示叉号只要把判断条件换一下就行。UNICHAR码位里比较实用的几个码位字符用途10004✓通过标记10006✖失败标记9733★等级标注9734☆等级标注8594→流程指向8730√数学根号3.3 CHAR(34)双引号公式里最容易被忽略的“符号搬运工”在Excel公式里如果要输出带双引号的文本直接写““”会非常麻烦因为双引号本身是公式里文本的定界符。这时候CHAR(34)就是救兵CHAR(34) A1 CHAR(34)这条公式可以把A1的内容包上一对双引号生成类似于某某的文本。这在拼SQL语句、拼JSON片段、生成带引号的导入文件时非常实用。同理CHAR(9)制表符可以用于生成TSV格式文本用公式把几列数据拼成一行A2 CHAR(9) B2 CHAR(9) C2这种格式在很多数据库导入工具里可以直接粘贴使用。3.4 动态符号与条件格式图标集到底选哪个这里多说一句我的体会。如果符号只是用来“看”的不参与后续统计条件格式的图标集更合适因为它不改动单元格本身的数据。比如“红绿灯”图标集直接基于数值大小显示颜色圆点完全不用写公式。但如果符号需要参与筛选、计数或者导出给别人用那就必须把符号作为真实字符写入单元格这时候用函数生成更合理。我一般的原则是数据本身要以字符形式存在时用公式只是辅助展示时用条件格式。4. 清洗脏数据时CHAR是利器看不见的字符才是大麻烦4.1 粘贴数据里最常见的四种“隐形字符”从网页、PDF、数据库导出的Excel数据表面上看着正常实际上藏着很多看不见的字符最常见的四类换行符CHAR(10)单元格文本中间莫名其妙“断行”实际是夹了换行符。回车符CHAR(13)老系统数据里的“硬回车”在单元格里经常显示成一个小方框。制表符CHAR(9)从网页表格复制出来的数据列中间可能有Tab。不间断空格CHAR(160)这个最坑它是空格的样子但TRIM函数清不掉。其中CHAR(160)我单独讲解一下。网页HTML里有一个字符叫“不换行空格”码位是160显示效果和普通空格几乎一样。从网页复制文字粘贴到Excel后这些字符会混进数据里。你看着是空格用Excel的替换功能输入一个空格去替换又发现替换不掉——因为替换框里输入的是CHAR(32)的普通空格而数据里是CHAR(160)。4.2 用SUBSTITUTE精确清理换行、回车和不间断空格处理这类问题核心是SUBSTITUTE函数它可以只替换指定的CHAR字符不影响其他字符。删除单元格内所有换行符SUBSTITUTE(A1,CHAR(10),)把换行符变成顿号适合把多行地址弄成一行SUBSTITUTE(A1,CHAR(10),、)清理不间断空格SUBSTITUTE(A1,CHAR(160),)处理从DOS系统导入数据时常见的CRLF混合换行可以连替两次SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),),CHAR(10),、)4.3 CLEAN和TRIM的盲区为什么有些空格就是清不掉Excel自带的清洗函数有两个但各有盲区TRIM只能清除普通空格CHAR(32)对CHAR(160)毫无办法。CLEAN可以清除ASCII控制字符包括换行符CHAR(10)和回车符CHAR(13)但同样对付不了CHAR(160)。所以遇到数据里怎么清都清不干净的空格不要怀疑自己操作有误先检查是不是出现了CHAR(160)。用下面的公式判断一下IF(ISNUMBER(SEARCH(CHAR(160),A1)),有不间断空格,无不间断空格)顺便提一个思路如果你在用SQL或Python处理数据替换特殊符号的原则和Excel完全一样。SQL里可以用regexp_replace把非字母数字的字符统一替换掉Python里用正则表达式 re.sub比如把连续空白字符压缩成一个空格。前提都是先把“看不见的字符”明确指认出来再谈清洗。4.4 隐藏字符排查的一个基础方法展示“隐形字符”有时候数据看着正常但LEN计算结果比肉眼看到的字符数多说明里面藏着东西。最暴力的排查方法就是用公式把字符逐字拆出来看。在新版Excel里用MID配合SEQUENCE可以一次性列出单元格里每个字符的码位UNICODE(MID(A1,SEQUENCE(LEN(A1)),1))这条公式会返回一个数组显示A1里每个字符的Unicode码位。你只要找到数值异常的码位再去查一下它对应什么字符问题就清楚了一半。5. 排查未知字符的完整链路用CODE和UNICODE“照妖”5.1 一个真实的案例看起来是数字SUM求和却是0有次同事找我说一个月的销售明细表里面金额列看着都是数字但SUM求和结果永远是0。我点进单元格看编辑栏里显示的也是数字没有任何异常。但用LEN测长度发现这个“数字”的长度比正常多1。再用公式查首字符码位UNICODE(MID(A1,1,1))结果是160。也就是说每个数字前面都带了一个不间断空格数据在Excel里被识别成了文本型数字SUM只能对数值求和自然返回0。处理方式也简单加一列公式把前面的CHAR(160)替换成空SUBSTITUTE(A1,CHAR(160),)*1最后一步乘以1是把文本型数字强制转成数值SUM立刻正常。这里如果你不加*1替换后仍然是文本SUM还是可能算不出结果。5.2 CODE和UNICODE怎么选别再傻傻分不清排查未知字符的时候CODE和UNICODE经常被搞混注意区分CODE返回当前字符集ANSI下的码位适合查ASCII字符和控制符。UNICODE返回Unicode码位适合查中文、特殊符号和超ANSI范围的字符。判断一个字符到底是换行符还是别的控制符用UNICODE就够了。只需要取文本的第一个字符做检测UNICODE(LEFT(A1,1))返回10就是换行符返回9是制表符返回160是不间断空格返回13是回车符。中文环境下遇到返回大于255的码位去查一下Unicode码表就能定位。5.3 MIDSEQUENCE组合一键列出单元格内所有字符的码位如果单元格里的隐形字符不止一个逐个用LEFT检测太低效。新版Excel的SEQUENCE函数配合MID可以一次性生成整串文本的码位清单UNICODE(MID(A1,SEQUENCE(LEN(A1)),1))选中公式所在单元格后Excel会自动溢出显示每个字符的码位。一眼扫过去哪里有异常数字哪里就有隐藏字符。如果你的Excel版本较老没有SEQUENCE老办法是用数组公式UNICODE(MID(A1,ROW(INDIRECT(1:LEN(A1))),1))输入后按CtrlShiftEnter。5.4 查找替换时怎么输入“看不见的字符”那个很好用的CtrlJ在Excel的查找和替换对话框里要查找换行符时直接在“查找内容”里输入空格或者粘贴是没用的。正确做法是在“查找内容”输入框里按CtrlJ。按下去之后输入框看起来是空的但实际已经输入了一个换行符。这个操作配合“替换为”留空可以一键删除整列数据里的所有换行符效果等同于SUBSTITUTE公式但好在不需要新增辅助列直接改原数据。同理如果要从外部导入的数据里批量删除回车符CHAR(13)在查找内容里按CtrlJ匹配到的是换行符回车符要另外处理。我常用的土办法是先记录一个含回车符的单元格复制它然后粘进查找框里再全部替换为空。最后分享一个我的个人习惯。我平时会专门在一张叫“常量”的表里维护常用的CHAR和UNICHAR映射比如码位10标记为“换行”160标记为“网页空格”甚至把CHAR(10)定义成自定义名称公式里直接写“换行”两个字代替。这样写公式时思路不会断维护起来也直白。CHAR函数本身不复杂复杂的是它背后牵扯的字符编码和脏数据问题。把自动换行、特殊符号、隐形字符清洗串起来想你会发现这其实是一套处理文本的完整思路先是能生成想要的字符然后能识别不想要的字符最后能自动化替换。这套思路在Excel里成立换到SQL、Python或别的数据处理工具里同样成立。