Does Excel have an operator similar to LIKE?
 

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

Does Excel have an operator similar to LIKE?
 

"19nigel91" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Does Excel have an operator similar to LIKE?
 

=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" wrote:

"19nigel91" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Does Excel have an operator similar to LIKE?
 

I'm out of ideas to streamline your IF, sorry. Hang around awhile. Maybe
other responders will have something to offer you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Does Excel have an operator similar to LIKE?
 

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

is what you are looking for.
--
Wag more, bark less


"19nigel91" wrote:

=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" wrote:

"19nigel91" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Does Excel have an operator similar to LIKE?
 

On May 25, 5:01 am, 19nigel91
wrote:
=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" wrote:
"19nigel91" wrote:
I am attempting to streamline an IF statement.
Is there an operator or argument that performs as LIKE?

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

Does Excel have an operator similar to LIKE?
 

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.

"nr" wrote:

On May 25, 5:01 am, 19nigel91
wrote:
=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" wrote:
"19nigel91" wrote:
I am attempting to streamline an IF statement.
Is there an operator or argument that performs as LIKE?

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

Does Excel have an operator similar to LIKE?
 

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

Does Excel have an operator similar to LIKE?
 

What problems did you have with my solution? Whatever the problem we can fix
it?
--
Wag more, bark less


"19nigel91" wrote:

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.

"nr" wrote:

On May 25, 5:01 am, 19nigel91
wrote:
=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" wrote:
"19nigel91" wrote:
I am attempting to streamline an IF statement.
Is there an operator or argument that performs as LIKE?

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

Does Excel have an operator similar to LIKE?
 

On Sun, 25 May 2008 05:14:01 -0700, 19nigel91
wrote:

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

Does Excel have an operator similar to LIKE?
 

Ron,
Thank you,
This is an elegant solution.

"Ron Rosenfeld" wrote:

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

Does Excel have an operator similar to LIKE?
 

On Sun, 25 May 2008 20:14:00 -0700, 19nigel91
wrote:

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