Excel pivot fields and trailing spaces

[Guest]

It appears to me that Excel pivot tables do not act per ANSI 92 in string
comparisons. My environment is MSO 2000 Excel, English(UK).
If I create a data table:
col1 col2
1 "abc"
2 "abc "
3 "abc "

and create a database query to a sheet based on this table, then filter the
data, the three strings in col2 are equal per ANSI 92 and appear as one item
in the filter drop down box.
If you create a pivot table based on the same data table, the field based
on col2 lists abc as three separate values, ie the spaces are significant.
Am I missing an application/operating system configuration that controls
this?

 

[Debra Dalgleish]

There's no setting you can change that will make these items equivalent
in a pivot table.

You could add another column to the source table, and use the TRIM
function to remove the extra spaces. Then, use that field in the pivot
table.