TRIM function "appears" to not be working...

[Eloise T]

Please take a look at the attached file regarding the TRIM function. Thank you.

 

[vletm]

1) Trim-function: Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
2) Check ALL Cells last character! In many cases, there is ' ' in the end of 'IT...'.
=> Trim-function works!

 

[Marc L]

In fact last character is not the same between A12 & A18 …
The same for A23 !

Just check with ASC function and you will see
it's not the space character ! TRIM only works with true spaces …
​

 

[Eloise T]

I inserted spaces at the end of the data in Column A and the "Duplicate" indicator disappeared where it once was. If TRIM is supposed to delete spaces....Why does it not?!

 

[Marc L]

Just remove any false or even true spaces at end of cells
'cause you did not well read or did not understand !
From your attachment, just check last character code …

After removing if you add true space at end of cells
and you use TRIM, you will see it works ‼

Your data seem poor web copy / paste …
​

 

[David Evans]

Depending on what specific "non-printing" characters you are having issues with, the following will give you some further techniques ...

https://support.office.com/en-us/ar...492-9ee1-1fb8388b9d14&ui=en-US&rs=en-US&ad=US

 

[Khalid NGO]

Hi,

Your range (without trim) and criteria (with trim) are Not Equal.

COUNTIF(s) will not allow range to be wrapped with Trim, but Sumproduct seems to be working here:

=IF(A6<>"",IF(SUMPRODUCT((TRIM(A$6:A$24)=TRIM(A6))+0)>1,"Duplicate",""),"--")

Regards,

 

[chirayu]

Simply put. You are only trimming 1 cell in your range of cells. That's why a match is not found. As 1 cell is trimmed & the other is not. Easiest way to do it would be to use a helper column. Which just uses a trim function. Then base your countifs on the helper column. At least that's the way I would do it

 

[vletm]

@Whatever
As I wrote in reply#2, You could read again ... especially blue word 'between'.
I took 'extra last characters' away and ...
I tried to highlight different colors and so on ...
How many duplicates do need/see?

 

[Eloise T]

Hi,

Your range (without trim) and criteria (with trim) are Not Equal.

COUNTIF(s) will not allow range to be wrapped with Trim, but Sumproduct seems to be working here:

=IF(A6<>"",IF(SUMPRODUCT((TRIM(A$6:A$24)=TRIM(A6))+0)>1,"Duplicate",""),"--")

Regards,

That fixed it! THANKS!