Why does the trim() function not working? This is so bizarre!

**tjj** · 08-10-2011, 02:19 AM

This is so bizarre! Please download the attachment and check it out. I am trying to get Excel to remove spaces at the end of a whole bunch of texts, but the trim() function is just not removing those spaces!

Download the attachment and look. I'm trying use the trim() function to remove the space at the end of the word Astros in cell A1. But after trimming it, the space is still there!

On cell A2 I personally typed out the word Houston Astros and then put a space at the end, and, and then did the same trim function and it works properly.

This leads me to believe that the space at the end of cell A1 is not really space after all. It has to be something else and not a space, that's why the trim function is not removing it. But if it is not a space, then what exactly is it??

How do I remove the space at the end of the word Astros in cell A1 using an Excel function?

Thanks

**DonkeyOte** · 08-10-2011, 02:47 AM

Your spaces are in fact non-breaking line feeds - you can verify this using:

=CODE(RIGHT(A1))

which will return 160 rather than 32 (space)

To account for this:

=SUBSTITUTE(A1,CHAR(160),"")