Excel 2002 : Why =TRIM() formula not working ?

[Mr. Low]

Hi,

Merry Christmas and Happy New Year.

I import a report in Excel format from another business system, but there
are always 6 empty spaces behind a column of reference say HG2145______.

I try to use =TRM() to eliminate the trailing spaces but always fail. I get
back the same thing. I could only do this by using =LEFT(A2,6)

May I know why and is there any other ways of getting what I want ?

Thanks

Low

 

[David Biddulph]

My guess is that the trailing characters are not actually spaces. Perhaps
non-breaking spaces [CHAR(160)]?

=CODE(RIGHT(A2)) will return 32 (Hex(20) if it's an ordinary space, but 160
if it's a non-breaking space.
You could try =SUBSTITUTE(A2,CHAR(160),"") if you want to get rid of the
nbsp characters.