Value ends with 999.99 or 999.00

[Hemant1992]

How can we filter values which ends with 999.99 or 999.00 like 123478999.99 or 123478999.00?

[CharlieS · Accepted solution]

Hi @Hemant1992 

I'm sure the Community will offer many ways to achieve this. I'll start things off with some string matching.

If we convert these values to string values, we just need to test if the last 3 characters are ".00" or ".99" This can be done with the following expression in a Filter tool (custom option):

Right(ToString([Value]),3) in (".00",".99")

Where [Value] is the field that contains your "123478999.99" value you'd like to test. 

[bpatel]

hi @Hemant1992 ,

if you convert the values to a string you can use the contains formula in the filter like this

i hope this helps!

[Hemant1992]

Hi @CharlieS 

I changed the formula as shown below and its working as required. Thanks for your help.

Right(ToString([Value]),6) in ("999.00","999.99")

[neilgallen · Accepted solution]

if you're concerned with any value that has three consecutive '9's before the decimal, then a regex match could apply.

REGEX_Match([Field],".*9{3}\..+$")

This allows for any length of characters before three consecutive 9's followed by a decimal point and at least one character after the decimal. By changing the value in the brackets you can account for any number of consecutive 9's.