3 Replies Latest reply on Jul 19, 2016 11:44 AM by Joe Oppelt

# If/Then Booleans and Strings

Hi Everyone,

I asked a question earlier about a formula that would convert the last 3 characters of an alphanumeric code I have to a number, and I got a good answer from one of the members who gave me the following formula:

IF INT(RIGHT([Code],3)) > 299 AND INT(RIGHT([Code],3)) < 999 THEN "Exclude" ELSE "Include" END

However, there are a few more stipulations that I need to add onto this, (just FYI, I'm filtering right at the data source, so it looks like I can't use the same field more than once in my direct filters from my MS SQL server):

In the case of my Code ending in a letter, most notably 'a' or 'b', it breaks the formula above, as it doesn't allow conversion to an integer, so I need to combine this with the clause above.  I tried this:

IF CONTAINS(RIGHT([Code],3),"a") OR IF CONTAINS(RIGHT([Code],3),"b") OR IF INT(RIGHT([Code],3)) > 299 AND INT(RIGHT([Code],3)) < 999 THEN "Exclude" ELSE "Include" END

It won't take the statement, any help would be greatly appreciated, thanks very much!

• ###### 1. Re: If/Then Booleans and Strings

When you say you are filtering "right at the data source", do you mean you have added a data source filter?  And are you using it on the "condition" tab of the filter.

Usually when I create a calc that results in "this" or "that", I put that calc on the filter shelf and select for whichever value I need for that sheet.

But when I create a data source filter, I result in a Boolean value and put it on the "condition".

The attached superstore example has a data source filter using the condition tab.  It grabs two specific values.  See attached.  (Vrersion 8.2 workbook.)

• ###### 2. Re: If/Then Booleans and Strings

Hi Joe,

Thanks for coming back so quickly, yes, I'm trying to isolate my population down by using the condition tab on the data source filter.  I don't want to use the filter shelf on a worksheet, because I'd like to isolate the correct population down to begin with.  I think I can mimic what you have in your example and reflect what I need in terms of criteria, but I'm still looking for an operator that basically denotes 'does not contain', that I can write into my formula.

• ###### 3. Re: If/Then Booleans and Strings

Try:  NOT CONTAINS(...

In the attached, edit the data source filter.  Un-comment the last line.  You will see that it will take out "South America" from the list it returns.