On Sat, 24 May 2008 21:48:00 -0700, 19nigel91
wrote:

I am attempting to streamline an IF statement. Is there an operator or
argument that performs as LIKE?

It's not clear from your message exactly what you want to test for.

Excel's wildcard characters are limited to
? (question mark) Any single character
* (asterisk) Any number of characters
~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde

It seems as if you want to test for the first seven characters being CA-AFG-
and, if they are, do a variety of actions depending on the next 2 or three
characters. And perhaps, if they are not, return the leftmost 9 characters.

However, a string such as "CA-AFG-4" is not defined.

Depending on the complete parameters of your problem, I would probably approach
it using Regular Expressions. Lorent Longre has a free add-in, morefunc.xll,
available at http://xcell05.free.fr/morefunc/english/ which can do multiple
substitutions.

Taking your example formula, I would do something like:

=LEFT(A25,REGEX.SUBSTITUTE(A25,"CA-AFG-((CJO)|(2-)|(EK)|(3-)|(3AX)).*","[2=12,3=8,4=11,5=8,6=10]"))

to get the parameter for the LEFT function.

Or, incorporating into a formula that returns a "9" if there is no match, then:

=LEFT(A25,IF(REGEX.COMP(A25,"CA-AFG-((CJO)|(2-)|(EK)|(3-)|(3AX)).*"),
REGEX.SUBSTITUTE(A25,"CA-AFG-((CJO)|(2-)|(EK)|(3-)|(3AX)).*","[2=12,3=8,4=11,5=8,6=10]"),9))

You can extend the Match possibilities quite a bit.

--ron