The trim function in Excel is a formula to take out leading and training spaces. It usually works amazingly well, you simply point the formula at the cell with the issue and it will produce a clean piece of text without any leading or training spaces. This article will focuses on what to do when the TRIM formula does not work as expected. It looks at alternatives to solve the problem.
Recently I was trying to cleanse a data set of trailing spaces. I knew they were there so I went to the TRIM formula. I was shocked to find it did not work. So looking into the matter it seems others have had the same problem. If the text was in A2 my formula looked like this.
=TRIM(A2)
However the formula failed. So I used the following
=TRIM(CLEAN(A2))
The above formula worked like a charm. The Excel CLEAN function looks at a text string and returns text that has been made clean of line breaks and any other non-printable characters
So when trim doesn’t work consider using the it in conjunction with the CLEAN formula.
