How to find a 4-char. consecutive string in a cell?

[Eloise T]

I am currently using the following formula to find a text string in a cell using the TRIM function to kill the spaces and the MID function to extract the string. I'm receiving the data containing the text string from many sources. Sometimes the submitters get sloppy and not only include unwanted spaces (the TRIM handily takes care of) but include other alphanumeric characters in front of (a problem) or behind (not a problem) the text string I'm wanting to isolate. Please see the attached file for clarification.

The formula: =IF(AND(H10=30,MID(TRIM(J10),1,3)="Tax"),30)

 

[vletm]

@Whatever
1) TRIM works fine
2) MID works fine
but
You try to find where is string "Tax"
1) "TAX" <> "Tax" , "tax" <> "Tax" ONLY "Tax" = "Tax"
2) There are 'i' and '7%' before 'Tax', Your MID don't work.
test this: =IF(AND(H10=30;FIND("Tax";J10;1)>0);30)

 

[Eloise T]

@Whatever
1) TRIM works fine
2) MID works fine
but
You try to find where is string "Tax"
1) "TAX" <> "Tax" , "tax" <> "Tax" ONLY "Tax" = "Tax"
2) There are 'i' and '7%' before 'Tax', Your MID don't work.
test this: =IF(AND(H10=30;FIND("Tax";J10;1)>0);30)

Your formula works as long as "Tax" is case-sensitive.
My formula is not case-sensitive and works only as long as there is nothing in front of the string.
I need to grab a pencil and figure out how to merge the two so that it is not only case-sensitive but also allows leading alpha-numerics in the text ...just as long as "tax" is in consecutive order.

 

[vletm]

@Whatever
You asked in #1 "Tax" ...
Okay, now all like "Tax" (TAX, tax, tAx ...)
=IF(AND(H10=30;FIND(UPPER("Tax");UPPER(J10);1)>0);30)

 

[Eloise T]

@Whatever
You asked in #1 "Tax" ...
Okay, now all like "Tax" (TAX, tax, tAx ...)
=IF(AND(H10=30,FIND(UPPER("Tax"),UPPER(J10),1)>0),30)

I would not have thought of using UPPER/LOWER/PROPER, but obviously it works!
Thank you!

 

[Nebu]

Hi:
If I understand correctly is this a much simpler formula.

=IF(AND(H10=30,SEARCH("*"&"tax"&"*",J10)>0),30,"")

Thanks

 

[Eloise T]

Hi:
If I understand correctly is this a much simpler formula.

=IF(AND(H10=30,SEARCH("*"&"tax"&"*",J10)>0),30,"")

Thanks

Simpler is always good. Thank you!

 

[vletm]

So, You should make it then six characters shorter...
=IF(AND(H13=30;SEARCH("*tax*";J13)>0);30;"")

 

[John Jairo V]

Hi!

What about this:
=30*(H10=30)*(SEARCH("tax",J10)>0)

Blessings!

 

[Eloise T]

So, You should make it then six characters shorter...
=IF(AND(H13=30;SEARCH("*tax*";J13)>0);30;"")

Six characters is not just 6 characters. Multiply that times 10,000+ rows and growing! THANK YOU!

 

[vletm]

Whatever - Okay.
I'm waiting the results of Your test of compare (... time, space or ... ? )
with or without 'six characters' (actually more)?
You can select any of those formulas, with lets say 10,001 rows?
I would like to know also how many characters
did You use with both ways for formulas?
Thank You.

 

[Eloise T]

Hi!

What about this:
=30*(H10=30)*(SEARCH("tax",J10)>0)

Blessings!

I have to keep the asterisks because no one just puts "tax."
It's taxi or itax or asdfTAX or 123TSXRATAX or ... Keeping it simple is just too easy for some people.