通过数组公式直接拆分字符 , 可以精确看到空白字符的位置 , 接下来 , 复制字符去替换即可 。
3.用substitute函数替换使用CTRL+H替换非常快捷 , 但是如果数据是身份证号码或长度大于11位的数字 , 一旦去除不可见字符 , 可能会导致格式直接变成科学计数 , 导致数据丢失 。
因此 , 可以使用函数来实现精准替换 。
上图可以是substitute函数的基本用法 , 直接使用left提取字符串第一位 , 也就是不可见字符来当查找值 , 实际查找值位置要根据你的表格实际来调整 。
或者也可以把不可见字符复制粘贴到记事本再复制回来 , 直接写在公式里 , 记得加\"\"号 。
但是上面的公式并未成功替换不可见字符 , 准确来说 , 只替换了一个 , 还剩结尾1个 。
干脆点 , 就直接再嵌套1个substitute函数 , 此时结果如下 。
=SUBSTITUTE(SUBSTITUTE(B2LEFT(B21)\"\")RIGHT(B21)\"\")
substitute函数返回结果默认文本 , 不用担心格式变化 。
如果你还是想用CTRL+H替换法 , 则需要提前用格式刷给身份证号码刷一个文本格式 。
红框中的文本格式是通过在单元格前加'单引号构成的 , 格式刷后会直接在身份证号前面也添加一个单引号 , 因此不会变形 。
总结一下?清洗Excel数据中不可见字符的主要逻辑:
- 定位不可见字符 , 复制它
- CTRL+H或者函数substitute替换
- Trim函数去除多余空格
- Clean函数去除非打印字符
- Len函数返回字符个数
- Left函数从左侧截取字符
- Mid函数从中间截取字符
- Right函数从右侧截取字符
相关经验推荐
- excel|备受吐槽的散热这次真的改好了?实测小米12S散热,结果出乎意料
- excel|Excel中数据去重无从下手? 这样做一键搞定,轻松而高效
- excel|目前最适合入手的四款手机,性价比极高,买到就是赚到
- excel|骁龙8+加持!小米12S续航稳了:信心十足随便挑战
- excel|Excel选择性粘贴,让办公简单化,可别只会ctrl+V?
- excel|Excel2019快捷键大全
- excel|Excel 表格筛选后,编号、总数、总价计算能否动态只统计可见行?
- iqoo|excel函数公式大全!让工作变得迅速高效
- 固态硬盘|超乎想象的Excel函数,你以为的并不是你以为的
- 鼠标|Excel技巧:这样做打印表格时把两页表格调整到一页,高效又实用