Excel文本函数速查:提取、替换、格式化常用写法_第1页
Excel文本函数速查:提取、替换、格式化常用写法_第2页
Excel文本函数速查:提取、替换、格式化常用写法_第3页
Excel文本函数速查:提取、替换、格式化常用写法_第4页
Excel文本函数速查:提取、替换、格式化常用写法_第5页
已阅读5页,还剩6页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

Excel文本函数速查:提取、替换、格式化常用写法项目内容文档编号EX-SOP-2026-005版本号V1.0编制角色办公效率组审核角色数据管理负责人生效日期2026年12月1日文本函数处理的是“看着一样、算不了、查不到”的数据。系统导出的姓名前后带空格,编码里混着分隔符,日期一列是“2026/7/3”另一列是“2026-07-03”——这些问题用文本函数批量清洗,比手工改快一个数量级。本文档按截取、定位、替换、连接、格式化、清洗六组组织,每个函数给语法、一张成品效果表(输入什么、公式怎么写、结果是什么)、以及结果不对时的改法。最后用员工信息清洗和订单标签生成两个完整示例,把六组函数串在一张表上同时使用。目录一、文本函数怎么选:先判断是截取、替换还是格式化二、截取:LEFT、RIGHT、MIDLEFT:从左侧截取固定位数RIGHT:从右侧截取固定位数MID:从任意位置截取任意长度三、定位:FIND、SEARCHFIND:区分大小写,找精确位置SEARCH:忽略大小写,支持通配符定位与截取的组合写法四、替换:SUBSTITUTE、REPLACESUBSTITUTE:按内容替换,不关心位置REPLACE:按位置替换,不关心内容SUBSTITUTE的嵌套与实例号五、连接:CONCAT、TEXTJOIN、&CONCAT:无分隔符的简单拼接TEXTJOIN:带分隔符、可跳过空白&:最灵活的逐项拼接六、格式化:TEXT、UPPER、LOWER、PROPERTEXT:给数字和日期“穿格式”UPPER、LOWER、PROPER:统一英文大小写七、清洗:TRIM、CLEAN、LENTRIM:去掉首尾空格,压缩中间连续空格CLEAN:删除不可打印字符LEN:字符数检查与截取前的定位八、完整示例员工信息清洗:从脏数据到规范登记表订单标签生成:截取编码加格式化加连接九、启动清单一、文本函数怎么选:先判断是截取、替换还是格式化拿到一列脏数据,先问三个问题。第一,我要的是一段文字还是一串数字的格式?要“从身份证号取出生日期”是截取,要“把日期显示成2026-12-01”是格式化。第二,位置是固定的还是随内容变化的?编码固定第5到8位是“从中间截取”,姓名中姓氏和名字之间的空格位置随姓名长短变化,需要先定位再截取。第三,改动的是内容还是格式?把“华东区”改成“华东”是替换内容,把“12800”显示成“12,800.00”是改格式。截取类(LEFT、RIGHT、MID)处理“取哪一段”,定位类(FIND、SEARCH)为截取提供位置参数,替换类(SUBSTITUTE、REPLACE)处理“改哪几个字”,连接类(CONCAT、TEXTJOIN、&)把分散的信息拼回去,格式化类(TEXT、UPPER、LOWER、PROPER)改变显示样式而不改变底层值,清洗类(TRIM、CLEAN、LEN)在一切操作之前先把“看着一样其实不一样”的脏字符去掉。一条通用规则:清洗先于一切。从系统导出或网页复制来的数据,先TRIM去首尾空格、CLEAN删不可打印字符,再做截取、替换和匹配。VLOOKUP查不到、条件格式颜色错位、SUM求和为0,很多情况下不是公式写错,而是数据里藏了看不见的空格或换行。二、截取:LEFT、RIGHT、MID1.LEFT:从左侧截取固定位数语法:=LEFT(单元格,截取位数)。从文本最左边开始,取指定数量的字符。位数省略时默认取1个。成品效果表(A列原始文本,B列写LEFT公式):行A列原始文本B列公式与结果说明213812345678=LEFT(A2,3)→138提取手机号前3位3ORD-2024-0315=LEFT(A3,3)→ORD提取订单编码前缀4张三丰=LEFT(A4,1)→张提取姓氏(中文一个字算一位)填错时怎么改:截出来的字数不对,先确认截取位数是从1开始数。中英文混排时一个汉字算一个字符,但显示宽度不同,不要按“看起来占了几个字母的宽度”来估位数。2.RIGHT:从右侧截取固定位数语法:=RIGHT(单元格,截取位数)。从文本最右边开始取。成品效果表:行A列原始文本B列公式与结果说明213812345678=RIGHT(A2,4)→5678提取手机号末4位做脱敏3ORD-2024-0315=RIGHT(A3,4)→0315提取订单日期部分4EMP-2026-001=RIGHT(A4,3)→001提取流水号末3位填错时怎么改:位数写多了会连前面的字符一起带出来。先用LEN看原文本总长度,确认要取的位数不超过总长。3.MID:从任意位置截取任意长度语法:=MID(单元格,起始位置,截取位数)。起始位置从1开始计数,大于文本总长度时返回空字符串。成品效果表(编码“PJ-2026-A03-005”):目标公式结果说明取项目号A03=MID(A2,9,3)A03从第9位起取3位取年份2026=MID(A2,4,4)2026从第4位起取4位取批次005=RIGHT(A2,3)005末3位,和MID配合使用填错时怎么改:起始位置写0返回#VALUE!,必须从1开始。位置数错是MID最常见的错误,先用LEN确认总长,再用FIND定位关键字符的位置,不要凭肉眼数。三、定位:FIND、SEARCH截取之前,经常需要知道“分隔符在第几位”或“某个字在哪里”。FIND和SEARCH解决这个问题。1.FIND:区分大小写,找精确位置语法:=FIND(查找字符,文本)。返回查找字符在文本中第一次出现的位置。区分大小写,不支持通配符。成品效果表(A列编码,B列找“-”的位置):行A列编码B列公式与结果说明2EMP-2026-001=FIND("-",A2)→4第一个“-”在第4位3ORD/2024/0315=FIND("/",A3)→4分隔符是“/”时找“/”填错时怎么改:返回#VALUE!说明文本里没有这个字符,先确认分隔符是半角还是全角。要找“-”但文本里是“—”,肉眼看着像,FIND认不出。2.SEARCH:忽略大小写,支持通配符语法:=SEARCH(查找字符,文本)。和FIND功能相同,区别在于忽略大小写,且查找字符中可以用问号代表单个字符、星号代表任意多个字符。成品效果表(A列文本,B列用SEARCH定位):行A列文本B列公式与结果说明2Excel2026=SEARCH("e",A2)→1忽略大小写,第一个E在1位3abc-def=SEARCH("?-",A3)→3问号匹配任意单字符,“c-”从第3位起填错时怎么改:需要精确区分大小写时不要用SEARCH,改用FIND。通配符用在有明确匹配模式时,不要用星号匹配一切。3.定位与截取的组合写法SEARCH或FIND返回位置后,把位置参数交给LEFT、RIGHT或MID,就能做到“按分隔符动态截取”,不受姓名长短或编码结构变化的影响。姓氏提取(空格之前的部分):=LEFT(A2,SEARCH("",A2)-1)。SEARCH找到空格位置,减1得到姓氏的末位。名字提取(空格之后的部分):=RIGHT(A2,LEN(A2)-SEARCH("",A2))。用总长度减去空格位置,得到名字的字符数。成品效果表(A列全名,B列取姓,C列取名):行A列全名B列公式与结果C列公式与结果2陈晓东=LEFT(A2,SEARCH("",A2)-1)→陈晓=RIGHT(A2,LEN(A2)-SEARCH("",A2))→东3刘洋=LEFT(A3,SEARCH("",A3)-1)→刘=RIGHT(A3,LEN(A3)-SEARCH("",A3))→洋填错时怎么改:返回#VALUE!说明文本里没有空格(或分隔符不一致)。先用=FIND("",A2)确认分隔符确实存在,再套截取公式。四、替换:SUBSTITUTE、REPLACE1.SUBSTITUTE:按内容替换,不关心位置语法:=SUBSTITUTE(文本,旧内容,新内容,[替换第几个])。把文本中指定内容替换成新内容,不关心它出现在第几位。第四个参数省略时替换所有出现,指定数字时只替换该次出现。成品效果表:行A列原始文本B列公式与结果说明2EMP/2026/001=SUBSTITUTE(A2,"/","-")→EMP-2026-001所有“/”换成“-”3ABCD=SUBSTITUTE(A3,"","-",2)→AB-CD只替换第2个空格4销售部-销售组=SUBSTITUTE(A4,"销售","业务")→业务部-业务组所有“销售”替换,两处都变填错时怎么改:只想替换第一个出现的,第四个参数写1;替换第二个写2。嵌套多个SUBSTITUTE可以一次性替换多种字符,示例:先把星号换成短横,再在结果上把井号换成空格,写成=SUBSTITUTE(SUBSTITUTE(A2,"*","-"),"#","")。SUBSTITUTE区分大小写,替换“urgent”不会命中“Urgent”。2.REPLACE:按位置替换,不关心内容语法:=REPLACE(旧文本,起始位置,替换字符数,新文本)。从第几位开始,替换掉几个字符,换成新内容。原位置上的内容是什么不重要。成品效果表:行A列原始文本B列公式与结果说明2Product123=REPLACE(A2,8,3,"456")→Product456第8位起3个字符换成4563ABCDEFG=REPLACE(A3,3,2,"XY")→ABXYEFG第3位起2个字符换成XY4EMP-0101=REPLACE(A4,1,4,"STF-")→STF-0101前缀从EMP改为STF填错时怎么改:起始位置和替换字符数都要数准。REPLACE适合“结构固定、只有某一段内容需要变”的场景(编码前缀、中间流水号),结构不固定时用SUBSTITUTE。3.SUBSTITUTE的嵌套与实例号SUBSTITUTE的实例号参数解决“只改其中一个出现”的问题。示例:ABC-ABC-ABC,要只改第二个ABC为XYZ,写=SUBSTITUTE(A2,"ABC","XYZ",2),结果为ABC-XYZ-ABC,第一和第三个不动。嵌套时注意执行顺序:内层先执行,外层对内层的结果再替换。五、连接:CONCAT、TEXTJOIN、&1.CONCAT:无分隔符的简单拼接语法:=CONCAT(区域或文本)。把多个单元格的内容直接粘在一起,不加分隔符,不处理空格。成品效果表(A列姓,B列名,C列拼接):行A列姓B列名C列公式与结果2陈晓东=CONCAT(A2,B2)→陈晓东3刘洋=CONCAT(A3,B3)→刘洋填错时怎么改:CONCAT会把空格原样粘进去,源数据有空格时结果里也有。需要跳过空白或加分隔符时改用TEXTJOIN。2.TEXTJOIN:带分隔符、可跳过空白语法:=TEXTJOIN(分隔符,是否忽略空白,区域)。第二个参数写TRUE时跳过空单元格,写FALSE时空格也连进去。成品效果表(A2:A5为工序,A4为空,C2写TEXTJOIN):行A列工序说明2切割3打磨4(空白)跳过5喷涂C2=TEXTJOIN("、",TRUE,A2:A5)切割、打磨、喷涂填错时怎么改:第二个参数写FALSE时空格也会连进去,出现连续顿号“切割、、打磨”就是这里写错了。分隔符要用英文双引号包住。3.&:最灵活的逐项拼接&把文本和公式结果逐项连起来,可以混合文字、单元格和TEXT的格式化结果。示例:="第"&A2&"周",A2为3,结果为“第3周”。和TEXT配合时,="截止日期:"&TEXT(B2,"yyyy-mm-dd"),把日期格式化后再拼进句子。&的优点是灵活,缺点是项数多时公式很长,能用TEXTJOIN区域引用的场景优先用TEXTJOIN。六、格式化:TEXT、UPPER、LOWER、PROPER1.TEXT:给数字和日期“穿格式”语法:=TEXT(数值或日期,"格式代码")。把底层值转成按指定格式显示的文本。格式代码用英文双引号包住。成品效果表:行A列原始值B列公式与结果说明22026/7/3=TEXT(A2,"yyyy-mm-dd")→2026-07-03统一日期格式312800=TEXT(A3,"#,##0.00")→12,800.00千分位加两位小数43=TEXT(A4,"第0周")→第3周自定义文字格式50.85=TEXT(A5,"0%")→85%小数转百分比文本填错时怎么改:TEXT的结果是文本,不能再直接参与加减乘除。需要计算时保留原始值计算,只在展示列用TEXT美化。格式代码里用了中文引号会报错,必须是英文双引号。2.UPPER、LOWER、PROPER:统一英文大小写UPPER全部转大写,LOWER全部转小写,PROPER每个单词首字母大写。语法分别是=UPPER(文本)、=LOWER(文本)、=PROPER(文本)。成品效果表(A列英文姓名,B列规范化):行A列原始B列公式与结果说明2alicechen=PROPER(A2)→AliceChen每个词首字母大写3BOBWANG=LOWER(A3)→bobwang全部小写4carolli=UPPER(A4)→CAROLLI全部大写填错时怎么改:PROPER会把“o'neil”变成“O'Neil”还是“O'neil”取决于版本处理,专有名词拼写需要人工核对,不要完全依赖PROPER。七、清洗:TRIM、CLEAN、LEN1.TRIM:去掉首尾空格,压缩中间连续空格语法:=TRIM(单元格)。去掉文本首尾的空格,把中间连续多个空格压缩成一个。成品效果表(A列带多余空格的姓名,B列清洗):行A列原始(空格用“·”示意)B列公式与结果2·陈··晓·=TRIM(A2)→陈·晓(首尾去净,中间压成一个)3·刘洋·=TRIM(A3)→刘洋填错时怎么改:TRIM只处理普通空格(ASCII32),去不掉网页复制来的不间断空格。那种用=SUBSTITUTE(A2,CHAR(160),"")处理。TRIM也不处理全角空格,全角空格需要用SUBSTITUTE按内容替换。2.CLEAN:删除不可打印字符语法:=CLEAN(单元格)。删除文本中前32个ASCII字符里的不可打印字符(换行、制表符、回车等)。从ERP、网页、PDF复制来的数据常带这些字符,导致“看着一样却查不到”。成品效果表(A列从系统导出的工号,B列CLEAN之后):行A列原始(含换行或制表符)B列公式与结果2EMP-0101(末尾有换行)=CLEAN(A2)→EMP-01013EMP-0102(中间有制表符)=CLEAN(A3)→EMP0102(制表符被删,字符紧贴)填错时怎么改:CLEAN删换行后长文本会连成一片,需要保留结构时先SUBSTITUTE把换行替换成空格,再用CLEAN。清洗匹配列的推荐顺序:先TRIM+CLEAN,再做VLOOKUP或条件格式。3.LEN:字符数检查与截取前的定位语法:=LEN(单元格)。返回字符数。中文一个字算1,英文一个字母算1。成品效果表(A列工号,B列检查位数):行A列工号B列公式与结果说明2EMP-0101=LEN(A2)→8位数正确3EMP-01021=LEN(A3)→9位数多了1位,提示检查4陈晓=LEN(A4)→2两个汉字算2位填错时怎么改:LEN统计的是字符数,不是字节数。需要区分中英文时用LENB(中文算2字节)与LEN的差值估算中文字符个数。截取公式里的“取几位”和“总长减多少”都要以LEN的结果为准,不要凭肉眼数。八、完整示例1.员工信息清洗:从脏数据到规范登记表填写前:从旧系统导出的员工表,A列工号带空格和换行,B列姓名含多余空格,C列是“部门-组”混排,D列入职日期格式混乱。行A列工号(脏)B列姓名(脏)C列部门组(混排)D列入职日期2EMP-0101(换行)·陈··晓·销售部-华东组2026/7/33EMP-0102(空格)·刘洋·技术部-平台组2026-07-154EMP-0103·周敏·财务部-核算组2026年8月1日填写中,在F到I列写清洗公式:F2工号清洗:=CLEAN(TRIM(A2)),去空格和不可打印字符。

G2姓名清洗:=TRIM(B2),去首尾多余空格。

H2部门提取:=LEFT(C2,FIND("-",C2)-1),取“-”之前的部分。

I2日期统一:=TEXT(D2,"yyyy-mm-dd"),把各种日期写法统一成标准文本。填写后:行F列清洗后工号G列清洗后姓名H列部门(提取)I列统一日期2EMP-0101陈晓销售部2026-07-033EMP-0102刘洋技术部2026-07-154EMP-0103周敏财务部2026-08-01清洗后的F列和G列可以直接用于VLOOKUP或条件格式,不会再因为空格和换行导致“看着一样查不到”。2.订单标签生成:截取编码加格式化加连接填写前:订单表有产品编码(PJ-2026-A03-005格式)、数量、单价,需要生成一行可直接打印的标签文字:“项目A03,数量12件,金额1,440.00元”。行A列编码B列数量C列单价2PJ-2026-A03-005121203PJ-2026-B01-00252

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论