If's conditions about matching

[0-0 Wai Wai ^-^]

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!

 

[Ron Coderre]

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

 

[Biff]

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,"Start",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"")

Biff

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

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!

 

[Biff]

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

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,"Start",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"")

Biff

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

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!

 

[0-0 Wai Wai ^-^]

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.

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.
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".
:-@(

 

[David Biddulph]

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")

 

[Ron Coderre]

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

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,"Start",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"")

Biff

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

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!

 

[Ron Coderre]

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?

*****
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")

*****
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)

****
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

 

[Biff]

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

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

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,"Start",IF(RIGHT(A1,LEN(C1))=C1,"End","Middle")),"")

Biff

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


:


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!