special usage of logical-test for IF function

[luckypurchase]

Dear reader,

I like to use the following function:
=IF(A1="tax";"non-purchase";" purchase")

Right now, Logical-test = equal to "tax"

Though, I like the logical-test to detect any kind of combination with the word "tax". example transportationtax must give me = true and so "non-purchase" will appear.

Thank you in advance

Bye
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=64></TD></TR></TBODY></TABLE>

 

[tc88]

Dear reader,

I like to use the following function:
=IF(A1="tax";"non-purchase";" purchase")

Right now, Logical-test = equal to "tax"

Though, I like the logical-test to detect any kind of combination with the word "tax". example transportationtax must give me = true and so "non-purchase" will appear.

Thank you in advance

Bye
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=64></TD></TR></TBODY></TABLE>

Are there a lot of combinations with the word "tax"? If it's only a few, I would just do an OR statement for your logical test. Also, you should be using commas, not semicolons, to separate your arguments like I have below.

=IF(OR(A1="tax",A1="transportationtax",A1="salestax"),"Non-Purchase","Purchase")

 

[mikerickson]

=IF(ISNUMBER(FIND("tax", A1)), "non-purchase", "purchase")

 

[tc88]

=IF(ISNUMBER(FIND("tax", A1)), "non-purchase", "purchase")

Just curious, how would ISNUMBER work there? I haven't used the ISNUMBER/ISERROR/ISBLANK functions much at all, but if it's in quotes ("tax") doesn't that indicate text?

 

[luckypurchase]

Are there a lot of combinations with the word "tax"? If it's only a few, I would just do an OR statement for your logical test. Also, you should be using commas, not semicolons, to separate your arguments like I have below.

=IF(OR(A1="tax",A1="transportationtax",A1="salestax"),"Non-Purchase","Purchase")

Yes there are about 22000 rows in columA with various words with tax (salestax, valueaddedtax, transportationtax etc). Moreover, words like fines etc. need to be find by the function as well.

Thank you in advance!

 

[mikerickson]

Just curious, how would ISNUMBER work there? I haven't used the ISNUMBER/ISERROR/ISBLANK functions much at all, but if it's in quotes ("tax") doesn't that indicate text?

FIND finds the position of a substring within another string.
If A1 contains "free tax", FIND("tax",A1) returns 6
If A1 contains "cat", FIND("tax", A1) returns a #VALUE error

ISNUMBER(FIND("tax",A1)) returns TRUE if A1 has "tax" as a substring.

 

[Peter_SSs]

Just a couple of comments:

1. I would suggest changing FIND to SEARCH in the formula suggested by Mike. So ..
=IF(ISNUMBER(SEARCH("tax", A1)), "non-purchase", "purchase")

We haven't seen your data but if it is possible that A1 contained "Sales Tax" then FIND("tax",A1) would not locate "Tax" but SEARCH("tax",A1) would.

Another option would be

=IF(COUNTIF(A1,"*tax*"),"non-purchase","purchase")

2. It is quite likely not possible with your data but these FIND/SEARCH/COUNTIF functions would also identity as "tax" even if A1 contained "I went by taxi".