| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| XOR operator in Excel | Excel Discussion (Misc queries) | |||
| have Excel retrieve conditional operator ( < = ) from a cell | Excel Worksheet Functions | |||
| Excel on the web similar to google spreadsheets | Excel Discussion (Misc queries) | |||
| MAJOR BUG: Excel operator precedence is wrong | Excel Worksheet Functions | |||
| Excel and googletools (or similar)? | Setting up and Configuration of Excel | |||
Linear Mode
