Does Excel have an operator similar to LIKE?

1

19nigel91

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

Max

19nigel91 said:
I am attempting to streamline an IF statement.
Is there an operator or argument that performs as LIKE?
Click to expand...

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
 
1

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-??-".
 
M

Max

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

Brad

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

is what you are looking for.
 
N

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-??-".
Click to expand...

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.
 
1

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.
 
R

Ron Rosenfeld

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

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
 
R

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.
Click to expand...

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
 
1

19nigel91

Ron,
Thank you,
This is an elegant solution.

Ron Rosenfeld said:
I am attempting to streamline an IF statement. Is there an operator or
argument that performs as LIKE?
Click to expand...

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
Click to expand...
 
R

Ron Rosenfeld

Ron,
Thank you,
This is an elegant solution.

"Ron Rosenfeld" wrote:
Click to expand...

You're welcome.

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