Help with Auto Filter "Ends With"

[Guest]

I have a column of prices. All sale items end with ".99". I want to return
all items that are on sale.

I'm trying to use auto filter to find all items that end with "99" or ".99".
When I set this up, it returns nothing. I have tried using ends with "9",
changing the format to number, text, general. I have also tried using the
"contains" funtion. Nothing works.

I saw an article about using helper columns, which is fine, but I don't know
a formula for "Ends with".

Any idea why not.

 

[Guest]

In a helper column type

=IF(MOD(A1,1)=0.99,A1,"")

Drag down to extract all prices ending in .99

Mike

 

[Guest]

Thanks- it works! Any idea why the Auto Filter command won't work?

 

[Guest]

actually- it works for cells that are less than $10, like $9.99, $1.99, etc.
Anything with additional digits, such as $39.99, does not work. I tried
changing =.0.99
to"
=.99
=00.99
=*.99
="*.99"

and a few other things, but couldn't get anything to work. Any suggestions?

 

[Dave Peterson]

Or just coerce those numbers to text:

=a2&""

And use ends with .99 when you filter that column.

 

[Peo Sjoblom]

Ends with is if you have a text string like

abcde

and you select ends with de for instance


It won't work using numbers, that's why you need a helper column or the
advanced filter


--
Regards,

Peo Sjoblom

 

[MyVeryOwnSelf]

I have a column of prices. All sale items end with ".99". I want

actually- it works for cells that are less than $10, like $9.99,
$1.99, etc. Anything with additional digits, such as $39.99, does not
work. I tried changing =.0.99
to"
=.99
=00.99
=*.99
="*.99"

There appears to be some rounding in the calculation.

Maybe something like this will help:
=IF(ABS(MOD(A1,1)-0.99)<0.0001,A1,"")