Solved: Using wildcards in a formula

[rrtts]

Hello all...I'm in the final stages of completing my project that many of you here helped create (whether you knew it or not). For that, I offer my sincere thanks.

Although I've learned lots, I've got a ways to go still.

One thing that continues to come up from time to time that I can't quite answer is whether you can use wildcard characters in a formula.

For example, right now I'm trying to code an IF/AND statement to get a result that is used to "conditionally format" a cell. No matter how I try to make it work...I just can't seem to get it. I've come up with a few work arounds - but I'm not totally achieving the results I want.

I don't have it composed in a spread sheet yet - so let me try to summarize.

If A1=B1 the result is 0. Easy enough.

=IF(A1=B1,0,1)

However, the problem is if A1 and B1 are not equal. Depending on the value of A1 and B1, I want the result to be a 1 or 2.

If A1=ABXX (where XX is = any letter i.e. ABAA ABAB ABAC etc) and B1=XYZ then the result will be 1. If A1=anything other than ABXX and B1=XYZ then the result will be 2.

So, what I'm trying to figure out is if it is possible to do somethng like:

=IF(A1=B1,0,IF(AND(A1="AB**",B1="XYZ"),1,2))

Yes, the actual values of A1 will be 4 letters and B1 3 letters - although the values may vary.

Any help would be greatly appreciated. Hopefully I clearly articulated what I'm trying to do.

[Bob Phillips]

If it starts with AB

=IF(A1=B1,0,IF(AND(LEFT(A1,2)="AB",B1="XYZ"),1,2))

if it contains AB

=IF(A1=B1,0,IF(AND(ISNUMBER(FIND("AB",A1)),B1="XYZ"),1,2))

[Bob Phillips]

If you are doing it in CF, you don't need the IFs

=AND(LEFT(A1,2)="AB",B1="XYZ")

and

=AND(ISNUMBER(FIND("AB",A1)),B1="XYZ")

[rrtts]

=IF(A1=B1,0,IF(AND(LEFT(A1,2)="AB",B1="XYZ"),1,2))

Brilliant!

xld does it again.

Thanks a bunch. Works perfect!