Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel pivot fields and trailing spaces
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? -- Fred |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel pivot fields and trailing spaces
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. Fred wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Query sometimes adds trailing spaces to fields | Excel Discussion (Misc queries) | |||
Excel 2002 : How to remove trailing spaces ? | Excel Discussion (Misc queries) | |||
How keep Leading/Trailing spaces when save in Excel? | Excel Discussion (Misc queries) | |||
Trailing spaces in Pivot tables | Excel Worksheet Functions | |||
Excel problem with trailing spaces | Excel Programming |