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

[ConneXionLost]

For scenario 1

Please Login or Register  to view this content.

For scenario 2

Please Login or Register  to view this content.

For scenario 3

Please Login or Register  to view this content.

Scenario 4 is not exactly clear. Do you mean "no K or no L", or "no K and no L"?

[trankim]

ConneXionLost,
Thank you for your input. However, I received the answer first from Rick Rothstein from ExcelFox.com came up with a solution that worked for me. I think all the formula ways works. Appreciate it a lot!


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*"),COUN TIF(A2,"*L*")),"",1),"")

[ConneXionLost]

So this is a crosspost? I'll remember that.

[trankim]

I apologize sincerely. I did not read that. What you state above makes complete sense and I will not cross post unless it cannot be solved in the other post.

[trankim]

Here is the cross post.

http://www.excelfox.com/forum/f2/if-...viors-583-new/

Again, apologies!