IF Contains formula - query

EtamSoko

hi Guys,

I am interested whether there is a way to avoid splitting the column DATA (attached) with text to column tool in case I want to identify lines that contains '5170030,5170031'. 

i tried the simplest

IF Contains([DATA], "5170030,5170031") THEN "ABC" ELSE "DEF" ENDIF

formula, but it would not work.

Thanks a lot for the support in advance!

MateK

if contains issue.xlsx

BenMoss

Hi @EtamSoko 

The issue here is that when you do a contains statement, it is looking for that specific section within the entire string.

In your case, there is more data contained between the two values, so this simple contains statement will not work.

I would probably recommend using RegEx if you are unwilling to split the data (though I'm not sure why, as this is simple and effective!).
5170041 (313158870 - 0.867), 5170043 (313158870 - 0.133)

Does not actually contain the string "5170030,5170031"

So we need to build a statement that accounts for the detail in between. If this detail is fixed then we can stick with our contains statement, but just add the other information in. If it varies, then RegEx can be used to account for this.

try

IF

regex_match([Data],"5170041.+, 5170043.+")

THEN "ABC" ELSE "DEF" ENDIF

Ben