Can't 'trim' or 'clean' spaces

**[email protected]** · 02-05-2010, 06:32 PM

Greetings, Gurus.

On a regular basis I have to upload cost to our order system. These cost are sent to me by the suppliers, and the part numbers have to match exactly, (from the excel sheet to the order system), or else the cost won't upload.

I have found that in some of my upload files there is a space, or sometimes several spaces, at the end of the part number. These spaces will screw up the upload. I alwyas use the "Replace" option to replace spaces with nothing, and that usually works.

In the attached sample file, there are 3 part numbers with a space at the end of the part number. I tried using the replace option. That didn't work. I also tried using the =TRIM() formula and the =CLEAN() formula, and neither of those removed the spaces.

Why can't I remove the spaces from the end of these part numbers?

**Richard Buttrey** · 02-05-2010, 06:41 PM

Hi,

Try in B2 and copied down. Then Copy and paste special values.

Please Login or Register  to view this content.

HTH

**[email protected]** · 02-05-2010, 06:51 PM

Thanks, Richard. That works! What is "CHAR(160)"?

**Richard Buttrey** · 02-05-2010, 07:05 PM

Hi

It's the non breaking space character, often used where you want to keep two words 'joined together', when otherwise they may split if at the end of a line.

Rgds