Filter numbers as text (2002 SP3)

Topic

New Reply

Hi y’all,
can anyone give me a clue as to how I go about filtering to see all rows that do not contain the string ‘0123 in the attached spreadsheet?

TIA

Reply | Quote

Replies

None of the cells in the spreadsheet you attached contain the string ‘0123’, so I don’t know what you need.

Reply | Quote

Silly me I meant to write ‘0321

Reply | Quote

Custom filter:
“does not contain”
0321

Seems to work

Steve

Reply | Quote

Ahh, I was trying “Does not equal”, which doesn’t seem to work although one would have thought it should.

Now is it possible to perform this filter using Advanced Filter”?
(The ultimate filter criteria will require the use of advanced filter)

Reply | Quote

Yes, use a criteria range that looks like this:

Criteria

=ISERROR(FIND(“0321”,B2))

(the formula must refer to the first row of the data)

Reply | Quote

It does not work since it converts the “text-number”(0123) to a number (123) in its routine.

Steve

Reply | Quote

So it seems that Excel triumphs again. Why can’t it just leave one’s data alone!

Reply | Quote

(Edited by Rudi on 10-May-05 11:08. Sorry, I misread your post…You want records that DO NOT contain 0321. In that case Hans’s formula will work. But you must still not have a column heading for formula criteria!)

Unless I’m missing the boat here, I was able to filter ‘0321 in a list by using =NOT(ISERROR(FIND(“0321”,B2))) as the criteria for the advanced filter. Remember to specify a blank cell as the column heading for a formula acting as criteria!

Reply | Quote

Thanks Rudi, maybe the blank colum header was the trick. It still shouldn’t be this difficult!

Any normal user would expect at anto filter of “does not equal” 0123 (which appears in the drop down list, to work, rather than having to use “does not contain”, which of course will not do exactly the same thing.

Thanks to all who helped reach this solution.

Reply | Quote

The problem is he wanted to do it with a custom autofilter not advanced filter.

The autofilter does not “work” the way most think it should in this regard…

Steve

Reply | Quote

Not according to post 477839

Paul needed the adv. filter!

Anyhow…the answer is available in both formats…to keep everyone happy!

Reply | Quote

Sorry. You are correct

I was only thinking about the original question and the original post…

Steve

Reply | Quote