Forum Discussion
How to remove invisible character
Please help to remove invisible character from Cell "B3". No additional space or any symbol is showing in cell "B3". But when we apply LEN function, it is giving 2 characters as below.
14 Replies
- I have had this issue a few times, specially when you export data from ERPs etc. There is a simple solution to fix this. First Select the data range you need to clean up. Then open"Find & Select -> Go to Special". (Keyboard shortcut -> Alt-H-F-D-S). Select the radio button against constants. Now there will be four boxes below ticked. Numbers, Text, Logicals, Errors. Just untick numbers and click ok. Now all the invisible and ghost characters containing cells would be highlighted. Just press delete and you are good to go. All the invisible chars are removed now.
- Find and Replace
Copy the character from the formula bar and paste in 'Find What'. Leave 'Replace with' empty. Try
=Value (Len(B3)) or NumberValue(Len(B3))
Or =--TRIM(CLEAN(SUBSTITUTE(B2,CHAR(10),"")))
Or
= Value(Substitute(B2," ",""))char160 is a hard-space (a non-breaking space). It's different from the regular space (char32), but -- to the naked eye -- they look identical (and invisible). 🙂
- Thanks, but It is not working...
This is not like Char160. this case we cannot see the character by way of space or any other.
Code is showing as 63 but it is not a character like "?" or "Ω"
Any other options?- Is this what you want.
I use simple PQ?
