TRIM Formula, Am I missing something?

[Hrishikesh Sandilya]

Sorry for asking a very basic question, but my TRIM formula is not working in the attached Excel sheet.

I have a list of email addresses and there are spaces around the email addresses. I want to remove the spaces by using TRIM.

but TRIM is somehow not working. I am wondering if I am missing something here.

 

[shrivallabha]

That is non-breaking space or Character 160. You can't chop it off by using TRIM formula.

Use alternative like:
=TRIM(SUBSTITUTE(C2,CHAR(160)," "))

 

[Luke M]

CHAR 160 is the bane of many of my formulas. Wish Microsoft would add it to the list of CLEAN's targets.

 

[Hrishikesh Sandilya]

Thank You Shrivallabha. CHAR 160 is something totally new for me. Thank you for teaching me to something new.

 

[Pavan.Sada.PS]

Hello Luke.. Just for my information.. How would a layman know it is a char 160.. is there a way to extract the char information for a cell with value

 

[Luke M]

The CODE function is the reverse of CHAR. You give it a character, it will tell you the code. So, if I wanted the check the 4th character, could do:
=CODE(MID(A2,4,1))
and that would tell me what character it is. Alternatively, if I wanted to check for any 160s, could do:
=ISNUMBER(SEARCH(CHAR(160),A2))
Which would just return true/false if the character is there.