Excel not filtering as expected

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Don't know if anyone's come across this before, but I would be glad of some
reassurance that it's not just me, and if anyone knows a way round it, that
would be even better!

Basically, we are using a spreadsheet to keep track of (amongst other
things) the history of our processes. One of the columns therefore may have
many entries within any one cell, reflecting the different dates on which
actions were taken. I know it's not ideal but I've inherited it and I'm
stuck with it for the time being. To make it easier to distinguish between
the date entries, I have used the Alt Enter combination to force a new line
within the cell.

However, when I filter on this column to see which cells contain eg DfES, it
doesn't pick up all of the appropriate cells. It may pick up four, but an
ordinary text search within the column may find twice as many on separate
lines (NOT cells where DfES occurs twice within the one cell).

I thought it might be something to do with the Alt Enter, but a quick test
suggests that it may not.

Has anybody come across a similar problem, and has anyone any thoughts on
correcting it?

TIA

Mike
 
Are there embedded spaces in the cells that don't show up?
"dfES" <> "dfES "

Generally, problems like these are caused by the data and not by how Excel
operates.
 
Tom

That must be close to a record for speed of reply! Thanks for your response,
but I'm pretty sure that the problem doesn't lie with embedded spaces.

I suspected that kind of problem when I first noticed what was happening, so
I have used copy and paste to insert the exact same text, taking it from the
contents of the cells.

I have also just run a check by having Excel tell me what characters and
their codes are in a few of the offending cells, but that just confirms that
there is nothing unusual about the contents (apart from the hard returns).

I'm completely at a loss. I can get round it by using the serach feature,
but it rather goes against the grain to do it the hard way, and anyway
filtering has the advantage of showing me just the data I want all together.

If you have any more ideas, I'ld be grateful to hear them.

Mike
 
The filter will only search the first 255 characters of the cell, so if
your codes are beyond that, they won't be found.
 
Back
Top