Auto Filter Issues

[Guest]

I have a list of products. some numerical and some with a 'suffix' at the
end eg
123
123a
123ab
124
124a
When I use auto filter (begins with or contains) the plain number is always
ommitted. Any ideas why?

 

[Guest]

Sadly that failed to work.

It's happening because you are asking if a cell begins with (or contains) 123
as text which the number 123 doesn't so it is filtered out.

To get around the problem format all the cells as text.

Mike

 

[Guest]

Thanks John.
Good solution.

strange one this - but if you put =TRIM(A2) and drag down to create a helper
column the auto filter then works on this new column.
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Stefano Gatto]

Applying the "Text" format will only impact the way Excel is *displaying* the
data, not storing it. This is why the problem remains. The Autofilter is not
able to consider numbers as text, even if it could and if this is the
expectation of the wider audience. On its side, the Edit/Find function will
find all cells containing sequence "1234", including in cells containing
numbers....!

Therefore what we need is to change the *type* of the data from number to
text. One way is to apply a function taking the number as an argument and
returning a text (like TRIM() or like CONCATENATE(), MID(), LEFT() etc).
I hope this helps.

Stefano Gatto