| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If's conditions about matching
How can I code these if's conditions: 1) If the first letter of the statement is "A" or number "2" 2) If the second word of the statement is "join" or "joins" or "joined" 3) If there's a phrase (eg paid by cash) in the statement 4) Whether the phrase appears at the start, in the middle, or at the end of the statement The position of the phrase: Statement 1: {phrase}... ... [start] Statement 2: ...{phrase}... [middle] Statement 3: ... ...{phrase} [end] Thank you! -- Additional info about my computer: - Office XP - Windows XP Pro |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
Try these:
For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "0-0 Wai Wai ^-^" wrote: How can I code these if's conditions: 1) If the first letter of the statement is "A" or number "2" 2) If the second word of the statement is "join" or "joins" or "joined" 3) If there's a phrase (eg paid by cash) in the statement 4) Whether the phrase appears at the start, in the middle, or at the end of the statement The position of the phrase: Statement 1: {phrase}... ... [start] Statement 2: ...{phrase}... [middle] Statement 3: ... ...{phrase} [end] Thank you! -- Additional info about my computer: - Office XP - Windows XP Pro |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
4)Whether the phrase appears at the start, in the middle, or at the end of
the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Returns #REF! when A1 = paid by cash in person A few keystrokes shorter and maybe less complicated: C1 = paid by cash =IF(ISNUMBER(SEARCH(C1,A1)),IF(SEARCH(C1,A1)=1,"St art",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"") Biff "Ron Coderre" wrote in message ... Try these: For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "0-0 Wai Wai ^-^" wrote: How can I code these if's conditions: 1) If the first letter of the statement is "A" or number "2" 2) If the second word of the statement is "join" or "joins" or "joined" 3) If there's a phrase (eg paid by cash) in the statement 4) Whether the phrase appears at the start, in the middle, or at the end of the statement The position of the phrase: Statement 1: {phrase}... ... [start] Statement 2: ...{phrase}... [middle] Statement 3: ... ...{phrase} [end] Thank you! -- Additional info about my computer: - Office XP - Windows XP Pro |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
I just thought of something........
If A1 contains only the phrase "paid by cash".......... Is it the "start" or "end" ? <bg When I first read this post this afternoon and before Ron had replied I thought the OP wanted all these conditions built into a single formula! I started laughing! Biff "Biff" wrote in message ... 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Returns #REF! when A1 = paid by cash in person A few keystrokes shorter and maybe less complicated: C1 = paid by cash =IF(ISNUMBER(SEARCH(C1,A1)),IF(SEARCH(C1,A1)=1,"St art",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"") Biff "Ron Coderre" wrote in message ... Try these: For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "0-0 Wai Wai ^-^" wrote: How can I code these if's conditions: 1) If the first letter of the statement is "A" or number "2" 2) If the second word of the statement is "join" or "joins" or "joined" 3) If there's a phrase (eg paid by cash) in the statement 4) Whether the phrase appears at the start, in the middle, or at the end of the statement The position of the phrase: Statement 1: {phrase}... ... [start] Statement 2: ...{phrase}... [middle] Statement 3: ... ...{phrase} [end] Thank you! -- Additional info about my computer: - Office XP - Windows XP Pro |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
"Ron Coderre" ¦b¶l¥ó ¤¤¼¶¼g... Try these: For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") Brilliant! I tried a similar function in vain. =IF(LEFT(A1,1)={"A","2"},"YES","NO") I tried to either press or not press "Ctrl+Shift+Enter", but I drew a blank. I still don't get why my function doesn't work. 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? Sorry, not okay. This function is malfunction. :P It is true as long as it can find the word "join*" (without quotes) anywhere but the first word in the statement. I wonder if there's any function which performs exact match. I'm think we could try to extract the second word out by the use of 1st & 2nd space. Then we may use EXACT to search for "join or joins or joined". But what function can we use to extract the second word? Does anyone know? 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") Thanks. :D However the same problem occurs as mentioned in Q2. We need exact match. 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Only the "start" and "end" works. If the phrase is in the middle, it returns "no". :-@( Is that something you can work with? *********** Regards, Ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
"0-0 Wai Wai ^-^" wrote in message
... "Ron Coderre" ¦b¶l¥ó ¤¤¼¶¼g... Try these: For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") Brilliant! I tried a similar function in vain. =IF(LEFT(A1,1)={"A","2"},"YES","NO") I tried to either press or not press "Ctrl+Shift+Enter", but I drew a blank. I still don't get why my function doesn't work. Try =IF(OR(LEFT(A1,1)="A",LEFT(A1,1)="2"),"YES","NO") -- David Biddulph |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
Hi, Biff
Actually the formula does work....When adjusting for screen wrapping, a space character is inadvertently eliminated from the 2nd COUNTIF function, causing the error you noticed. Broken into 4 pieces, this is the formula: B1: =INDEX({"NO","START","MIDDLE","END"}, COUNTIF($A1,$C$1&"*")*2 +COUNTIF($A1,"* "&$C$1&" *")*3 +COUNTIF($A1,"* "&$C$1)*4) That being said....I prefer your shorter formula; it's more concise. Amusing that both formulas have different issues with various placements of "paid in cashews" <g *********** Best Regards, Ron XL2002, WinXP "Biff" wrote: 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Returns #REF! when A1 = paid by cash in person A few keystrokes shorter and maybe less complicated: C1 = paid by cash =IF(ISNUMBER(SEARCH(C1,A1)),IF(SEARCH(C1,A1)=1,"St art",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"") Biff "Ron Coderre" wrote in message ... Try these: For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "0-0 Wai Wai ^-^" wrote: How can I code these if's conditions: 1) If the first letter of the statement is "A" or number "2" 2) If the second word of the statement is "join" or "joins" or "joined" 3) If there's a phrase (eg paid by cash) in the statement 4) Whether the phrase appears at the start, in the middle, or at the end of the statement The position of the phrase: Statement 1: {phrase}... ... [start] Statement 2: ...{phrase}... [middle] Statement 3: ... ...{phrase} [end] Thank you! -- Additional info about my computer: - Office XP - Windows XP Pro |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
Wai Wai:
Let's see how I do this time: 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? Sorry, not okay. ***** Try this (connect the 3 sections): B1: =IF(OR(ISNUMBER(SEARCH({" join "," joined "," joins "},A1&" "))) ,IF(MIN(SEARCH({" join "," joined "," joins "},A1&" join joined joins "))= SEARCH(" ",A1),"YES","NO"),"NO") 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") However the same problem occurs as mentioned in Q2. We need exact match ***** Try this: With C1: PAID IN CASH B1: =IF(ISNUMBER(SEARCH(" "&$C$1&" "," "&A1&" ")),"YES","NO") 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Only the "start" and "end" works. If the phrase is in the middle, it returns "no". **** See my response to Biff for an explanation of why the formula failed on "middle". However, since I like Biff's formula better, I adjusted it to make it work correctly in all situations (including no match for "paid in cashews") Join the 3 pieces of this formula: B1: =IF(ISNUMBER(SEARCH(" "&C1&" "," "&A1&" ")) ,IF(SEARCH(" "&C1&" "," "&A1&" ")=1,"Start" ,IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"NO") There are no leading or trailing spaces in any of the above formula sections. Does that help? *********** Regards, Ron XL2002, WinXP "0-0 Wai Wai ^-^" wrote: "Ron Coderre" ¦b¶l¥ó ¤¤¼¶¼g... Try these: For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") Brilliant! I tried a similar function in vain. =IF(LEFT(A1,1)={"A","2"},"YES","NO") I tried to either press or not press "Ctrl+Shift+Enter", but I drew a blank. I still don't get why my function doesn't work. 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? Sorry, not okay. This function is malfunction. :P It is true as long as it can find the word "join*" (without quotes) anywhere but the first word in the statement. I wonder if there's any function which performs exact match. I'm think we could try to extract the second word out by the use of 1st & 2nd space. Then we may use EXACT to search for "join or joins or joined". But what function can we use to extract the second word? Does anyone know? 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") Thanks. :D However the same problem occurs as mentioned in Q2. We need exact match. 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Only the "start" and "end" works. If the phrase is in the middle, it returns "no". :-@( Is that something you can work with? *********** Regards, Ron |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If's conditions about matching
Actually the formula does work....When adjusting for screen wrapping
OK, works just fine. Dang line wrap! "paid in cashews" Is that like working for peanuts? I have experience in that area! I like your modified version, padding with spaces, to cure the cashews problem. Biff "Ron Coderre" wrote in message ... Hi, Biff Actually the formula does work....When adjusting for screen wrapping, a space character is inadvertently eliminated from the 2nd COUNTIF function, causing the error you noticed. Broken into 4 pieces, this is the formula: B1: =INDEX({"NO","START","MIDDLE","END"}, COUNTIF($A1,$C$1&"*")*2 +COUNTIF($A1,"* "&$C$1&" *")*3 +COUNTIF($A1,"* "&$C$1)*4) That being said....I prefer your shorter formula; it's more concise. Amusing that both formulas have different issues with various placements of "paid in cashews" <g *********** Best Regards, Ron XL2002, WinXP "Biff" wrote: 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Returns #REF! when A1 = paid by cash in person A few keystrokes shorter and maybe less complicated: C1 = paid by cash =IF(ISNUMBER(SEARCH(C1,A1)),IF(SEARCH(C1,A1)=1,"St art",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"") Biff "Ron Coderre" wrote in message ... Try these: For text in A1 1)If the first letter of the statement is "A" or number "2" B1: =IF(OR(LEFT(A1,1)={"A","2"}),"YES","NO") 2)If the second word of the statement is "join" or "joins" or "joined" Maybe this: B1: =IF(ISNUMBER(SEARCH("join",A1,SEARCH(" ",A1))),"YES","NO") Note: this one will also match on "joint". Is that ok? 3)If there's a phrase (eg paid by cash) in the statement B1: =IF(ISNUMBER(SEARCH("paid in cash",A1)),"YES","NO") 4)Whether the phrase appears at the start, in the middle, or at the end of the statement B1: =INDEX({"NO","START","MIDDLE","END"},COUNTIF($A1,$ C$1&"*")*2+COUNTIF($A1,"* "&$C$1&" *")*3+COUNTIF($A1,"* "&$C$1)*4) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "0-0 Wai Wai ^-^" wrote: How can I code these if's conditions: 1) If the first letter of the statement is "A" or number "2" 2) If the second word of the statement is "join" or "joins" or "joined" 3) If there's a phrase (eg paid by cash) in the statement 4) Whether the phrase appears at the start, in the middle, or at the end of the statement The position of the phrase: Statement 1: {phrase}... ... [start] Statement 2: ...{phrase}... [middle] Statement 3: ... ...{phrase} [end] Thank you! -- Additional info about my computer: - Office XP - Windows XP Pro |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count using 2 conditions, one of which being a "less than or equal to" - URGENT | Excel Discussion (Misc queries) | |||
| COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Count matching cells | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Linear Mode
