If formula to look for Letter Patterns or Behaviors

[trankim]

I am trying to find a behavioral pattern. Therefore, I am hoping some IF formulas will work. Each formula would be a "scenario" and be in its own column. Thank you in advance for any advice you can give.

1) Where "J" is before "K" then put a "1". If "K" precedes "J", then it's value is "2". Everythng else should be blank

2) Where "J" is before "L" but does not include "K". If True, put a "1". If false, leave blank

3) Where "J" is before "K" and both are before "L". Must contain all three letters to be true. If true, then put a "1". Leave Blank if false

4) Where "J" but no "K" or no "L" then "1" if True.

D
DH
DHJ
DHJCK
DKJ
DJL
DJKL
DHGKL
K
L
KL

[Rick Rothstein]

These formulas should do what you want...

1) =IF(COUNTIF(A2,"*J*K*"),1,IF(COUNTIF(A2,"*K*J*"),2,""))

2) =IF(COUNTIF(A2,"*J*L*"),IF(COUNTIF(A2,"*K*"),"",1),"")

3) =IF(COUNTIF(A2,"*J*K*L*"),1,"")

4) =IF(COUNTIF(A2,"*J*"),IF(OR(COUNTIF(A2,"*K*"),COUNTIF(A2,"*L*")),"",1),"")

[Admin]

Hi

There may be better ways..

=IFERROR(LOOKUP(3,CHOOSE({1,2,3,4},IF(AND(ISNUMBER (SEARCH("j",A2)),OR(NOT(ISNUMBER(SEARCH("k",A2))),NOT(ISNUMBER(SEARCH("l",A2))))),1,""),IFERROR(IF(AND(SUM(SEARCH({"j","k"},A2)*{1,-1})<0,ISNUMBER(SEARCH("l",MID(A2,SEARCH("k",A2),255)))),1,""),""),IFERROR(IF(AND(SUM(SEARCH({"j","l"},A2)*{1,-1})<0,NOT(ISNUMBER(SEARCH("k",A2)))),1,""),""),IFERROR(IF(SUM(SEARCH({"j","k"},A2)*{1,-1})<0,1,2),""))),"")

[trankim]

Rick,
THANK YOU THANK YOU THANK YOU.....YOU are a EXCEL GENIUS. Saved me countless headaches! thanks again.

[trankim]

Admin,
Wow, your formula is COMPLEX. It looks like it could do all in one formula vs the way I had asked originally, which was to put each formula in its own column. I will attempt to understand your formula as it truly is mind blowing. you guys are super excel geniuses!!!!

thanks