Excel通配符批量替换教程 一秒搞定相似内容

通配符到底是什么不少人使用Excel好些年了, 一听闻“通配符”这三个字, 便认为那是只有程序员才会用到的事物。实际上它格外简单哪——唯有两个符号: 问号“?”以及星号“*”。问号象征着随便一个字符, 星号象征着不定数量的字符。比如说你想要把“张三”、“张四”、“张五”统统变为“张先生”, 径直查找“张?”替换为“张先生”便可。你无需逐个去寻觅, Excel会自动进行匹配所有姓张且名字仅有两个字的内容在实际的操作情形当中, 我目睹过超多的人手持一大批与此相类似的数据去进行手动更改。举例来说, 像是几千行地址里面的“路”以及“街”得要统一起来, 又或者产品编号里面某一段的数字需要替换成统一的代号。有的人甚至还把数据复制进到记事本之内, 利用替换功能以后再黏贴回来, 反反复复折腾长达半小时。但实际上运用通配符进行替换, 仅仅几分钟便能够大功告成。为什么替换时总是没效果很多人踩过一个坑。你在查找框里输入“ ”试图以此代表任意内容 , 然而Excel却什么都没找到。这是由于通配符得激活后才可使用。在Excel的查找替换对话框里面 , 默认状况下周惺和问号属于普通字符 , 你必须先勾选“单元格匹配” , 或者将“查找范围”设置成“值” , 如此才会生效。仍有部分人碰到需要替换的内容当中本就含有星号或者问号的实例, 像是产品名称里存在某单个引号符号。此情形下直接替换会把尽数内容都予以更改。解决的办法是于星号之前增添个波浪号“~”, 写作“~”。这般设定实际上是为了使得Excel能够分明分辨哪一个是通配符哪一个是普通符号。记不住亦不要紧, 真正遭遇这种状况时, 在百度上搜索“Excel波浪号转义”便可找寻到答案。再有一种情形是替换之后格式紊乱了, 举例而言, 在你原本的单元格之中存有数字、文本交杂在一起的状况, 替换结束之际却陡然转变为日期或者科学计数法了。这一般并非通配符所引发的问题, 却是起因于Excel自动识别数据类型而造成的。解决路径颇为简便, 在进行替换前所需要做的, 是先将目标列设置成文本格式, 或者在替换完成之后手动对格式实施更改。复杂场景怎么做批量替换太慢的话若是单次替换, 那, 可以用公式。比如说, 你要把一列地址之中所有的“上海市”改成“上海”, 还要把所有的“北京市”, 改成“北京”, 手写替换就得重复操作几十次。这个时候, 能够配合SUBSTITUTE函数, 加通配符思路去做。然而, 函数里不能够直接用星号通配, 需要结合LEFT、RIGHT、MID这些函数去截取关键位置。比如说, 你的数据呈现“2023年 - 销售部 - 像张三一般此格式”, 你计划将“销售部”变更为之“市场部”。设若部门名称长度稳定不变, 径直运用查找连同替换是最为便捷的。不过要是部门名称长短并不相同一, 好比“销售部”以及“售后服务部”杂糅在一起, 那你就需要仰仗多个替换行为。最为妥善的办法是首先实行分列操作, 先把部门单独拆分出来, 予以统一修改完成之后再进行合并还原回去, 对此补充一句, 此过程结束后, 最终数据得以妥善处理。https://www.hongxiu.com/booklist/1504594https://www.hongxiu.com/booklist/1504593https://www.hongxiu.com/booklist/1504592https://www.hongxiu.com/booklist/1504591https://www.hongxiu.com/booklist/1504590https://www.hongxiu.com/booklist/1504589https://www.hongxiu.com/booklist/1504588https://www.hongxiu.com/booklist/1504587https://www.hongxiu.com/booklist/1504586https://www.hongxiu.com/booklist/1504585https://www.hongxiu.com/booklist/1504584https://www.hongxiu.com/booklist/1504583https://www.hongxiu.com/booklist/1504582https://www.hongxiu.com/booklist/1504581https://www.hongxiu.com/booklist/1504580https://www.hongxiu.com/booklist/1504579https://www.hongxiu.com/booklist/1504578https://www.hongxiu.com/booklist/1504577https://www.hongxiu.com/booklist/1504576https://www.hongxiu.com/booklist/1504575https://www.hongxiu.com/booklist/1504574https://www.hongxiu.com/booklist/1504573https://www.hongxiu.com/booklist/1504572https://www.hongxiu.com/booklist/1504571https://www.hongxiu.com/booklist/1504570https://www.hongxiu.com/booklist/1504569https://www.hongxiu.com/booklist/1504568https://www.hongxiu.com/booklist/1504567https://www.hongxiu.com/booklist/1504566https://www.hongxiu.com/booklist/1504565https://www.hongxiu.com/booklist/1504564https://www.hongxiu.com/booklist/1504563https://www.hongxiu.com/booklist/1504562https://www.hongxiu.com/booklist/1504561https://www.hongxiu.com/booklist/1504560https://www.hongxiu.com/booklist/1504559https://www.hongxiu.com/booklist/1504558https://www.hongxiu.com/booklist/1504557https://www.hongxiu.com/booklist/1504556https://www.hongxiu.com/booklist/1504555https://www.hongxiu.com/booklist/1504554https://www.hongxiu.com/booklist/1504553https://www.hongxiu.com/booklist/1504552https://www.hongxiu.com/booklist/1504551https://www.hongxiu.com/booklist/1504550https://www.hongxiu.com/booklist/1504549https://www.hongxiu.com/booklist/1504548https://www.hongxiu.com/booklist/1504547https://www.hongxiu.com/booklist/1504546https://www.hongxiu.com/booklist/1504545https://www.hongxiu.com/booklist/1504544https://www.hongxiu.com/booklist/1504543https://www.hongxiu.com/booklist/1504542https://www.hongxiu.com/booklist/1504541https://www.hongxiu.com/booklist/1504540https://www.hongxiu.com/booklist/1504539https://www.hongxiu.com/booklist/1504538https://www.hongxiu.com/booklist/1504537https://www.hongxiu.com/booklist/1504536https://www.hongxiu.com/booklist/1504535https://www.hongxiu.com/booklist/1504534https://www.hongxiu.com/booklist/1504533https://www.hongxiu.com/booklist/1504532https://www.hongxiu.com/booklist/1504531https://www.hongxiu.com/booklist/1504530https://www.hongxiu.com/booklist/1504529https://www.hongxiu.com/booklist/1504528https://www.hongxiu.com/booklist/1504527https://www.hongxiu.com/booklist/1504526https://www.hongxiu.com/booklist/1504525https://www.hongxiu.com/booklist/1504524https://www.hongxiu.com/booklist/1504523https://www.hongxiu.com/booklist/1504522https://www.hongxiu.com/booklist/1504521https://www.hongxiu.com/booklist/1504520https://www.hongxiu.com/booklist/1504519https://www.hongxiu.com/booklist/1504518https://www.hongxiu.com/booklist/1504517https://www.hongxiu.com/booklist/1504516https://www.hongxiu.com/booklist/1504515https://www.hongxiu.com/booklist/1504514https://www.hongxiu.com/booklist/1504513https://www.hongxiu.com/booklist/1504512https://www.hongxiu.com/booklist/1504511https://www.hongxiu.com/booklist/1504510https://www.hongxiu.com/booklist/1504509https://www.hongxiu.com/booklist/1504508https://www.hongxiu.com/booklist/1504507https://www.hongxiu.com/booklist/1504506https://www.hongxiu.com/booklist/1504505https://www.hongxiu.com/booklist/1504504https://www.hongxiu.com/booklist/1504503https://www.hongxiu.com/booklist/1504502https://www.hongxiu.com/booklist/1504501https://www.hongxiu.com/booklist/1504500https://www.hongxiu.com/booklist/1504499https://www.hongxiu.com/booklist/1504498https://www.hongxiu.com/booklist/1504497https://www.hongxiu.com/booklist/1504496https://www.hongxiu.com/booklist/1504495https://www.hongxiu.com/booklist/1504494https://www.hongxiu.com/booklist/1504493https://www.hongxiu.com/booklist/1504492https://www.hongxiu.com/booklist/1504491https://www.hongxiu.com/booklist/1504490https://www.hongxiu.com/booklist/1504489https://www.hongxiu.com/booklist/1504488https://www.hongxiu.com/booklist/1504487https://www.hongxiu.com/booklist/1504486https://www.hongxiu.com/booklist/1504485https://www.hongxiu.com/booklist/1504484https://www.hongxiu.com/booklist/1504483https://www.hongxiu.com/booklist/1504482https://www.hongxiu.com/booklist/1504481https://www.hongxiu.com/booklist/1504480https://www.hongxiu.com/booklist/1504479https://www.hongxiu.com/booklist/1504478https://www.hongxiu.com/booklist/1504477https://www.hongxiu.com/booklist/1504476https://www.hongxiu.com/booklist/1504475https://www.hongxiu.com/booklist/1504474https://www.hongxiu.com/booklist/1504473https://www.hongxiu.com/booklist/1504472https://www.hongxiu.com/booklist/1504471https://www.hongxiu.com/booklist/1504470https://www.hongxiu.com/booklist/1504469https://www.hongxiu.com/booklist/1504468https://www.hongxiu.com/booklist/1504467https://www.hongxiu.com/booklist/1504466https://www.hongxiu.com/booklist/1504465https://www.hongxiu.com/booklist/1504464https://www.hongxiu.com/booklist/1504463https://www.hongxiu.com/booklist/1504462https://www.hongxiu.com/booklist/1504461https://www.hongxiu.com/booklist/1504460https://www.hongxiu.com/booklist/1504459https://www.hongxiu.com/booklist/1504458https://www.hongxiu.com/booklist/1504457https://www.hongxiu.com/booklist/1504456https://www.hongxiu.com/booklist/1504455https://www.hongxiu.com/booklist/1504454https://www.hongxiu.com/booklist/1504453https://www.hongxiu.com/booklist/1504452https://www.hongxiu.com/booklist/1504451https://www.hongxiu.com/booklist/1504450https://www.hongxiu.com/booklist/1504449https://www.hongxiu.com/booklist/1504448https://www.hongxiu.com/booklist/1504447https://www.hongxiu.com/booklist/1504446https://www.hongxiu.com/booklist/1504445https://www.hongxiu.com/booklist/1504444https://www.hongxiu.com/booklist/1504443https://www.hongxiu.com/booklist/1504442https://www.hongxiu.com/booklist/1504441https://www.hongxiu.com/booklist/1504440https://www.hongxiu.com/booklist/1504439https://www.hongxiu.com/booklist/1504438https://www.hongxiu.com/booklist/1504437https://www.hongxiu.com/booklist/1504436https://www.hongxiu.com/booklist/1504435https://www.hongxiu.com/booklist/1504434https://www.hongxiu.com/booklist/1504433https://www.hongxiu.com/booklist/1504432https://www.hongxiu.com/booklist/1504431https://www.hongxiu.com/booklist/1504430https://www.hongxiu.com/booklist/1504429https://www.hongxiu.com/booklist/1504428https://www.hongxiu.com/booklist/1504427https://www.hongxiu.com/booklist/1504426https://www.hongxiu.com/booklist/1504424https://www.hongxiu.com/booklist/1504425https://www.hongxiu.com/booklist/1504423https://www.hongxiu.com/booklist/1504419https://www.hongxiu.com/booklist/1504420https://www.hongxiu.com/booklist/1504422https://www.hongxiu.com/booklist/1504421https://www.hongxiu.com/booklist/1504417https://www.hongxiu.com/booklist/1504418https://www.hongxiu.com/booklist/1504416https://www.hongxiu.com/booklist/1504415https://www.hongxiu.com/booklist/1504414https://www.hongxiu.com/booklist/1504413https://www.hongxiu.com/booklist/1504412https://www.hongxiu.com/booklist/1504411https://www.hongxiu.com/booklist/1504410https://www.hongxiu.com/booklist/1504409https://www.hongxiu.com/booklist/1504408https://www.hongxiu.com/booklist/1504407https://www.hongxiu.com/booklist/1504405https://www.hongxiu.com/booklist/1504406https://www.hongxiu.com/booklist/1504404https://www.hongxiu.com/booklist/1504402https://www.hongxiu.com/booklist/1504403https://www.hongxiu.com/booklist/1504401https://www.hongxiu.com/booklist/1504400https://www.hongxiu.com/booklist/1504399https://www.hongxiu.com/booklist/1504398https://www.hongxiu.com/booklist/1504397https://www.hongxiu.com/booklist/1504396https://www.hongxiu.com/booklist/1504395https://www.hongxiu.com/booklist/1504390https://www.hongxiu.com/booklist/1504394https://www.hongxiu.com/booklist/1504393https://www.hongxiu.com/booklist/1504392https://www.hongxiu.com/booklist/1504391https://www.hongxiu.com/booklist/1504389https://www.hongxiu.com/booklist/1504387https://www.hongxiu.com/booklist/1504388https://www.hongxiu.com/booklist/1504386https://www.hongxiu.com/booklist/1504385https://www.hongxiu.com/booklist/1504384https://www.hongxiu.com/booklist/1504383https://www.hongxiu.com/booklist/1504382https://www.hongxiu.com/booklist/1504381https://www.hongxiu.com/booklist/1504378https://www.hongxiu.com/booklist/1504380https://www.hongxiu.com/booklist/1504379https://www.hongxiu.com/booklist/1504375https://www.hongxiu.com/booklist/1504377