Forum Discussion

Mannai_Accounts's avatar
Mannai_Accounts
Copper Contributor
Mar 23, 2022

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.   ...
excel
Donald_Genes_'s avatar
Donald_Genes_
Brass Contributor
Mar 23, 2022

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). 🙂

Mannai_Accounts's avatar
Mannai_Accounts
Copper Contributor
Mar 23, 2022
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?
    • Samson_Kaziko's avatar
      Samson_Kaziko
      Copper Contributor
      Oct 18, 2022

      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

    • Mannai_Accounts's avatar
      Mannai_Accounts
      Copper Contributor
      Mar 23, 2022
      Yes this also tried.. nothing happend.
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Mar 23, 2022
        ???

        You presumably used CODE() in the formula in E3. What is the result if you use UNICODE() instead?
  • Donald_Genes_'s avatar
    Donald_Genes_
    Brass Contributor
    Mar 23, 2022

    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's avatar
      Mannai_Accounts
      Copper Contributor
      Mar 23, 2022
      Can you please help if I send the excel sheet personally? if yes please share your email.
      Thanks..
      • Donald_Genes_'s avatar
        Donald_Genes_
        Brass Contributor
        Mar 23, 2022
        Send the file to me I will will use Power query or VBA to clean it

        Email address removed

Resources