excelexcel-2016

removing invisible character in excel

I notice in my excel I have an extra space in random places. So I copy the value and save it in notepad++. then I open using HEX Editor. so I found out it is actually not a space (HEX 20)

So the blank space is actually HEX C2. which is  But in excel this is invisible.

I am using Excel 2016

How can I get rid of this?

they are scattered randomly ... thank you

Solution

  • Try using substitute with char formula to remove the extra space. I guess you have to use char(160) which is blank space.

    =SUBSTITUTE("Cell Reference",CHAR(160),"")

    If you dont know the code you can use below formula to get the char code of your blank text.

    =CODE(RIGHT("Your Cell",1))