数组公式使用方法
- 需提前选中B1:M1区域,因为需要承接拆分的字符,可以尽可能大一点。
- 再输入数组公式
- 最后需要按数组确认键CTRL SHIFT 回车 确认公式
通过数组公式直接拆分字符,可以精确看到空白字符的位置,接下来,复制字符去替换即可。
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函数从右侧截取字符
看到这个了嘛,求一个