How to remove invisible character

Copper Contributor

Mannai_Accounts_1-1648020129300.png

 

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.

 

Mannai_Accounts_2-1648020247082.png

 

13 Replies

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

 

 

 

@Mannai_Accounts 

Did you use UNICODE()?

 

Can you please help if I send the excel sheet personally? if yes please share your email.
Thanks..
Yes this also tried.. nothing happend.
???

You presumably used CODE() in the formula in E3. What is the result if you use UNICODE() instead?
Send the file to me I will will use Power query or VBA to clean it

Email address removed
UNICODE()=8236
Now you have the code and can remove the character with UNICHAR() and SUBSITUTE().
Is this what you want.
I use simple PQ?

@Donald_Genes_ may you please assist with how to remove invisible characters on a file. You m,ay assist with using power query for the same if possible but at the end of the day i am trying to find a way to remove invisble characters.  I am not sure how i can attach a file here.

 

Thank you very much

Find and Replace
Copy the character from the formula bar and paste in 'Find What'. Leave 'Replace with' empty.