Excel 2007 - bug - filter removing rows with blanks

[boatman]

I import tab separated ASCII files into Excel 2007 to check conversion data.
For some fields there is a blank (space bar) between the tabs, for others
nothing (null).
When I apply a filter to a column that contains cells with blanks, the data
item I want to filter out (unchecked) disappears, but it also takes out the
rows with the blanks too, even though the 'blanks' item remains unchecked!
This problem does not occur if the cells contain nulls; the rows remain
visible.

Test this out by creating records in a simple tab separated text file in
notepad.

col 1 tab col 2 tab col 3
A tab B tab C
D tab blank tab E
F tab G tab tab

Save the file as a .txt file. Open it with Excel 2007 and apply a filter. In
col 2 if you filter out 'B' only the line 3 will remain; it filters out line
2 containing the blank as well. In col 3 if you filter out 'C', both lines 2
and 3 will remain.

Ifyou do a custom filter to exclude 'B' in col 2, lines 2 and 3 are left;
the row with the blank is untouched, as expected.

MSoft guys. This needs a fix.

 

[Jan Karel Pieterse]

Hi Boatman,

I import tab separated ASCII files into Excel 2007 to check conversion data.
For some fields there is a blank (space bar) between the tabs, for others
nothing (null).
When I apply a filter to a column that contains cells with blanks, the data
item I want to filter out (unchecked) disappears, but it also takes out the
rows with the blanks too

This is not a bug in my opinion. You tell Excel to show blanks, but a cell
containing a space is NOT blank, it contains a space. There is no way for Excel
to know if the space is deliberate content or not.
To avoid this, do a search and replace, look for entire cells and replace a
single space with nothing.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[boatman]

Jan,

I agree that a cell containing a blank and a null are different. However,
when I deselect just a single value using the autofilter, I only want rows
with that single value deselected from the view; I don't want the rows with
blanks in the cells deselected too.

Excel 2007 does treat blank and null cells the same using the autofilter
dropdown selection dialogue. In a column containing blanks in the cells, if I
deselect Blanks in the autofilter, the blank cell rows are dropped from the
view. In a column containing nulls in the cells, if I deselect Blanks in the
autofilter, the null cell rows are dropped from the view. I have not tested a
mixed (blanks and nulls) column full of data.

If I use the custom option in the autofilter on a column containing
blanks in the cells and use 'not equals' a non blank value, the rows
containing cells with blanks are left in view.

In my opinion the bug lays in the autofilter dropdown selection dialogue.
Somehow it is passing two deselections, the original and a blank.

I have not tested to see if this occurs with multiple deselects.

Boatman