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.
    • 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..

Resources