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.

In a helper column type

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

Drag down to extract all prices ending in .99

Mike

"Retail Services" wrote:

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.

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

"Retail Services" wrote:

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.

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?

"Mike H" wrote:

In a helper column type

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

Drag down to extract all prices ending in .99

Mike

"Retail Services" wrote:

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.

Or just coerce those numbers to text:

=a2&""

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



Retail Services wrote:

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.

--

Dave Peterson

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





"Retail Services" wrote in
message ...
Thanks- it works! Any idea why the Auto Filter command won't work?

"Retail Services" wrote:

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.

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".

In a helper column type

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

Drag down to extract all prices ending in .99

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,"")