Does Excel have an operator similar to LIKE?

[19nigel91]

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

 

[Max]

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

One example: =COUNTIF(A:A,"*"&"berry"&"*")
This counts cells in col A which contain the text: "berry" within,
like: mulberry, berry good, merry berry, 123 berry 456

---

 

[19nigel91]

=IF((LEFT(A25,10)="CA-AFG-CJO"),LEFT(A25,12),IF((LEFT(A25,9)="CA-AFG-2-"),LEFT(A25,8),IF((LEFT(A25,9)="CA-AFG-EK"),(LEFT(A25,11)),IF((LEFT(A25,9)="CA-AFG-3-"),LEFT(A25,8),IF((LEFT(A25,10)="CA-AFG-3AX"),LEFT(A25,10),IF((LEFT(A25,9))<>"CA-AFG-2-",LEFT(A25,9),""))))))

This is the formula I am trying to streamline, instead of having to
reference the seperate locations, I wanted to do something to the tune of
="CA-AFG-??-".

 

[Max]

I'm out of ideas to streamline your IF, sorry. Hang around awhile. Maybe
other responders will have something to offer you.

---

 

[Brad]

One solution
=IF(LEFT(A25,7)&MID(A25,10,1)="CA-AFG--",.......)

is what you are looking for.

 

[nr]

=IF((LEFT(A25,10)="CA-AFG-CJO"),LEFT(A25,12),IF((LEFT(A25,9)="CA-AFG-2-"),LEFT(A25,8),IF((LEFT(A25,9)="CA-AFG-EK"),(LEFT(A25,11)),IF((LEFT(A25,9)="CA-AFG-3-"),LEFT(A25,8),IF((LEFT(A25,10)="CA-AFG-3AX"),LEFT(A25,10),IF((LEFT(A25,9))<>"CA-AFG-2-",LEFT(A25,9),""))))))

This is the formula I am trying to streamline, instead of having to
reference the seperate locations, I wanted to do something to the tune of
="CA-AFG-??-".

Are you trying to test for the first seven characters to be "CA-
AFG-"? If so, you could use the LEFT function. Or, are you
interested in what follows the "AFG-" characters? If this is the
case, then you might create a cell value equal to that part of the
value, then use that cell's value in your IF statement.

If you provide more specific details, I'll try to help.

 

[19nigel91]

These are location codes. They range from CA-AFG-CJO-S1-0001 TO
CA-AFG-2-0001. The formula is effective but a bit rough. I NEED to maintain
the CA-AFG-CJO-S and also the CA-AFG-2. I need to maintain everything before
the -0001.

 

[Ron Rosenfeld]

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

 

[Brad]

What problems did you have with my solution? Whatever the problem we can fix
it?

 

[Ron Rosenfeld]

These are location codes. They range from CA-AFG-CJO-S1-0001 TO
CA-AFG-2-0001. The formula is effective but a bit rough. I NEED to maintain
the CA-AFG-CJO-S and also the CA-AFG-2. I need to maintain everything before
the -0001.

If all you want to do is remove the end parameter, in this hyphen separated
string, then all you need is:

=LEFT(A25,FIND(CHAR(1),SUBSTITUTE(A25,"-",
CHAR(1),LEN(A25)-LEN(SUBSTITUTE(A25,"-",""))))-1)

which will return everything up to but not including the last hyphen
--ron

 

[19nigel91]

Ron,
Thank you,
This is an elegant solution.

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

 

[Ron Rosenfeld]

Ron,
Thank you,
This is an elegant solution.

"Ron Rosenfeld" wrote:

You're welcome.

See my other response, also, for a different solution depending on what,
exactly, you want.
--ron