Determine if certain characters are in a text string???

Hi there,

I'm writing a very long if statement formula. I need to add in a line that basically says if there is a "Q*" somewhere in the text, return a "-". It's not always in the same location in the text string so I'm having trouble figuring out a formula. I'd like to keep it as short and simple as possible since it's already a very long if statement and the size of my excel file is getting out of hand :S

Thanks for the help!!

You can use this too :
Case sensitive :

Please Login or Register  to view this content.

Not case sensitive :

Please Login or Register  to view this content.

I actually need it to look for if there is an asterisk followed by Q, but I guess it's picking up to look for if it starts with a Q or has a Q at all... is there anyway to get around this?!

Can you provide an example? Then your requirements will be clear.

If you are trying to search whether it has Q*, then replace Q with Q* in the formula.

Sure. This is the current formula:

=IFERROR(IF(AND(LEFT(AB2,1)=" ")*OR(LEFT(K2,4)="YPSD",LEFT(K2,4)="YPSA"),"-",IF(AND(LEFT(AB2,1)=" ")*OR(LEFT(P2,4)="YPSD",LEFT(P2,4)="YPSA"),"-",IF(AND(TODAY()>AF2)*OR(LEFT(K2,3)="YPS",LEFT(K2,3)="MTL"),"Shares",IF(AND(TODAY()>AF2)*OR(LEFT(P2,3)="YPS",LEFT(P2,3)="MTL"),"Cash",IF(AND(TODAY()>AF2)*OR(LEFT(K2,3)<>"YPS",LEFT(K2,3)<>"MTL"),"External PA",IF(AND(TODAY()>AF2)*OR(LEFT(P2,3)<>"YPS",LEFT(P2,3)<>"MTL"),"External PA",IF(AND(LEFT(D222,3)="MAT",LEFT(F222,3)<>"YPS*",LEFT(F222,3)=LEFT(P222,3)),"-","-"))))))),"-")

What I need to add to it is if cell C2 contains a "Q*", "CAD", or "USD" in it I want it to return "External PA" (I had simplified for the sake of the post). I tested this formula =IF(ISNUMBER(SEARCH("*Q",C2)), "Yes", IF(ISNUMBER(SEARCH("USD",C2)),"Yes",IF(ISNUMBER(SEARCH("CAD",C2)),"Yes","No"))), but it was returning yes every time there was a capital Q in column C, as opposed to only returning a yes if there was a Q and an asterick.

Did you try that ?

Please Login or Register  to view this content.

That doesn't work because I need this piece at the very front of the formula (because of the error value I would essentially have to put the value if false before the value if true, when I need the value if false to be second so I can continue the formula).

I didn't get you. What is the problem if you replace the "Your formula" with you own formula? Keep the other parts as same as it is now.

Here ISERROR is checking if the FIND function returning any error. If Find couldn't find any "Q*" here then it will execute your formula, else it will show "-".

Be sure that, I am using ISERROR, not IFERROR.

@ Tony Valko :
Your formula will not work for him as he stated here that "Q*" will not be at same position.

Originally Posted by cchap

I need to add in a line that basically says if there is a "Q*" somewhere in the text, return a "-". It's not always in the same location in the text string so I'm having trouble figuring out a formula.

Yes. Because he already stated that at Post#1.

My formula should work for him. But may be he didn't get the difference between ISERROR and IFERROR. In his way he should try this :

Please Login or Register  to view this content.

May be he will understand then.

Maybe this ...

=IF(OR(ISNUMBER(SEARCH({"Q~*","USD","CAD"},C2))), "Yes", "No")

Sorry. It will be

Please Login or Register  to view this content.

None of these seem to be working...or rather I can't seem to get that formula to work within my greater if statement. I've tried organizing it a few ways and it's returning "FALSE" or "Yes" every time.

I ended up adding a separate column to check the conditions:

=IFERROR(IF(ISNUMBER(SEARCH("USD",C2)), "USD",IF(ISNUMBER(SEARCH("CAD",C2)),"CAD",IF(ISNUMBER(SEARCH("~*Q",C2)),"Q","-"))),"-")

Then modified the second formula to:
=IFERROR(IF(AS2="Q","-",IF(AND(LEFT(D2,3)="MAT",LEFT(F2,3)<>"YPS*",LEFT(F2,3)=LEFT(P2,3))*OR(AS2="CAD",AS2="USD"),"MM",IF(AND(LEFT(AB2,1)=" ")*OR(LEFT(K2,4)="YPSD",LEFT(K2,4)="YPSA"),"-",IF(AND(LEFT(AB2,1)=" ")*OR(LEFT(P2,4)="YPSD",LEFT(P2,4)="YPSA"),"-",IF(AND(TODAY()>AF2)*OR(LEFT(K2,3)="YPS",LEFT(K2,3)="MTL"),"Shares",IF(AND(TODAY()>AF2)*OR(LEFT(P2,3)="YPS",LEFT(P2,3)="MTL"),"Cash",IF(AND(TODAY()>AF2)*OR(LEFT(K2,3)<>"YPS",LEFT(K2,3)<>"MTL"),"External PA",IF(AND(TODAY()>AF2)*OR(LEFT(P2,3)<>"YPS",LEFT(P2,3)<>"MTL"),"External PA","-")))))))),"-")

It's probably not the best approach since it adds another 1000+ lines of formula but I couldn't seem to get anything else to work.

can you upload a sample file?

May be you can try this :

Please Login or Register  to view this content.