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.
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?Strange
Take the data to Power Query and let power Query automatically detect
Make sure you Trim the column
Or use Add Custom Column
Text.Select([Column A], {"0".."9"})Also copy the entire range of data and paste on another sheet using AlT H VV
Let's see what's wrong with the excel worksheet
- Find and Replace
Copy the character from the formula bar and paste in 'Find What'. Leave 'Replace with' empty. - 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.
