去除Excel中的不可见字符,就这么几步... | 您所在的位置:网站首页 › excel怎么删除空格和不可见字符 › 去除Excel中的不可见字符,就这么几步... |
0
分享至
用微信扫码二维码 分享至好友和朋友圈 粉丝群里有同学遇到了表格中不可见字符问题,今天一篇文章教你各种去除不可见字符方法。 先说下为什么会出现不可见字符: 大多是从某些系统里下载导出的Excel表格,部分日期或者数据,由于编码格式等问题,产生了不可见字符或者空格。⭐通常出现在字符串的首尾。导致的后果有vlookup无法正确匹配,函数公式或者计算操作无法正常进行等等。 「去除异常字符是我们进行数据清洗中的重要一环」 1.空格去除例如这种下载的数据中每个姓名之间存在空格,可以通过替换法或者trim函数剔除。 替换法选中需要替换的数据区域,按CTRL+H打开替换窗口,查找值输入空格,替换值不输入,全部替换,则去除数据中的所有空格。 替换效果 Trim函数则直接使用=trim(单元格)即可返回去除多余空格的数据。 不可见字符分两种情况,一种是非打印字符。 以ASCII码表为例,ASCII码值在0-31的为控制字符,无法显示和打印,比如回车键。如果你觉得表格中存在非打印字符,可以复制表格数据粘贴到TXT记事本中,如果出现其他字符和空格,则代表存在非打印字符。 Excel中去除方法很容易,使用CLEAN函数直接去除即可。 使用方法与上文的Trim函数一致。 另一种就是使用clean函数无法去除的不可见字符。比如下图,使用clean函数后仍然显示字符数存在2个额外字符。 我们就可以采取替换法或者直接取值法来去除,不过首先需要先定位不可见字符,找到它。 2.1 定位不可见字符「通过光标依次移动来判断不可见字符位置」 双击单元格,进入数据编辑界面,此时看到闪动的光标。按键盘上的右方向键→,依次向右移动光标。 如果明明按了右方向键,光标却没有移动,则说明这里存在一个不可见字符。 由于不可见字符通常难以用鼠标选取,则可以通过函数left、mid、right函数来直接提取。 例如上图案例,我们发现第一个字符就是不可见的,直接在空白单元格输入=left(A2,1)提取不可见字符。 接下来,只需要全部替换这个不可见字符为空值即可。 「通过数组公式来拆分字符串」 数组公式如下: =MID(A1,TRANSPOSE(ROW(1:12)),1) 通过数组公式直接拆分字符,可以精确看到空白字符的位置,接下来,复制字符去替换即可。 3.用substitute函数替换使用CTRL+H替换非常快捷,但是如果数据是身份证号码或长度大于11位的数字,一旦去除不可见字符,可能会导致格式直接变成科学计数,导致数据丢失。 因此,可以使用函数来实现精准替换。 上图可以是substitute函数的基本用法,直接使用left提取字符串第一位,也就是不可见字符来当查找值,实际查找值位置要根据你的表格实际来调整。 或者也可以把不可见字符复制粘贴到记事本再复制回来,直接写在公式里,记得加""号。 但是上面的公式并未成功替换不可见字符,准确来说,只替换了一个,还剩结尾1个。 干脆点,就直接再嵌套1个substitute函数,此时结果如下。 =SUBSTITUTE(SUBSTITUTE(B2,LEFT(B2,1),""),RIGHT(B2,1),"") substitute函数返回结果默认文本,不用担心格式变化。 如果你还是想用CTRL+H替换法,则需要提前用格式刷给身份证号码刷一个文本格式。 红框中的文本格式是通过在单元格前加'单引号构成的,格式刷后会直接在身份证号前面也添加一个单引号,因此不会变形。 总结一下⭐清洗Excel数据中不可见字符的主要逻辑: 定位不可见字符,复制它CTRL+H或者函数substitute替换还有两个专门清洗空格和非打印字符的函数。 Trim函数去除多余空格Clean函数去除非打印字符以及检测字符数量和截取字符的函数: Len函数返回字符个数Left函数从左侧截取字符Mid函数从中间截取字符Right函数从右侧截取字符看到这个了嘛,求一个 特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。 Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services. /阅读下一篇/ 返回网易首页 下载网易新闻客户端 |
CopyRight 2018-2019 实验室设备网 版权所有 |