Countif or if statement with ? wildcard

  • Hi All, I have search various ways, but could not find an answer to this question. Any ideas would be great!


    I have a list of unlimited possibilities and need to identify where a specific string is in a column. Okay here we go....


    I believe I need to be able to do some sort of IF statement that will give me a 1 if the 1 is exactly where I need it with the exact number of characters behind it.


    I tried version of the following, =COUNTIF(A8,"1????"), =IF($A8<>"100??","1","")


    So in the attachment, for column B, if 1 has four characters after it, give me a 1. In column D, if 1 has two characters after it, give me a 1, in column E, if a 1 has no characters behind it, give me a 1. I have tried COUNTIFs and IFs to no avail. Please help.



    Thank you,
    TerryLynn

  • Re: Countif or if statement with ? wildcard


    In B2


    =IF(MID(A2,1,1)="1",1,"")


    In C2


    =IF(MID(A2,2,1)="1",1,"")


    In D2


    =IF(MID(A2,3,1)="1",1,"")


    In E2


    =IF(MID(A2,5,1)="1",1,"")


    Then copy down to end of data.


    If you also need to include the case where there is 1 character after the 1 then this single formula in B2 copied across to column F and down to end of data will work


    =IF(MID($A2,COLUMN(A:A),1)="1",1,"")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Countif or if statement with ? wildcard


    Perfect! Thank you both! I think I messed around with (MID) sometime ago and forgot about it. Thanx so much!!

  • Re: Countif or if statement with ? wildcard


    You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!