How to check multiple cells for a single instance of a capital letter?

**bubernak** · 03-25-2013, 01:23 PM

Need help figuring out how to check multiple cells for a single (at least one) instance of a capital letter? So I thought I would use something like this

=IF(OR(COUNTIF(AU51:AU60,"D")=0,COUNTIF(AU155:AU158,"D")=0),"D","")

but the problem is that some of the cells can also contain a lower case "d", so the check is failing...and again, all I need to make sure is that there is at least one capital "D" in any of the cells. Thanks!

**FDibbins** · 03-25-2013, 01:25 PM

Hi and welcome to the forum

try replacing "D" with CHAR(68)

**bubernak** · 03-25-2013, 01:31 PM

Changed it to

=IF(OR(COUNTIF(AT51:AT60,CHAR(68) )=0,COUNTIF(AT155:AT158,CHAR(68) )=0),"D","")

but it still counts the lower case d's??

**icestationzbra** · 03-25-2013, 01:35 PM

you cannot use "D" with COUNTIF that way - for two reasons - you need to wrap it with wildcard (*) and COUNTIF does not regard case.

try this ARRAY (see my signature) formula:

=IF(OR(ISNUMBER(FIND("D",AU51:AU60))+ISNUMBER(FIND("D",AU155:AU158))),"D","-")

FIND is case-sensitive, SEARCH is not...

**FDibbins** · 03-25-2013, 01:50 PM

I changed your references, change them back. I was playing with this, but I'm not sure what your expected answer would be...

=IF(ISERROR(OR(FIND("D",B3:B8,1)=0,FIND("D",C3:C8,1)=0)),"","D") (find() is case-sensitive)

If that isnt what you want, can you upload a sample workbook, showing your data (no sensitive info), and some sample answers?

**FDibbins** · 03-25-2013, 01:50 PM

I changed your references, change them back. I was playing with this, but I'm not sure what your expected answer would be...

=IF(ISERROR(OR(FIND("D",B3:B8,1)=0,FIND("D",C3:C8,1)=0)),"","D") (find() is case-sensitive)

If that isnt what you want, can you upload a sample workbook, showing your data (no sensitive info), and some sample answers?

**judgeh59** · 03-25-2013, 02:00 PM

=IF(OR(EXACT(A:A,"D"),EXACT(B:B,"D")),"D","")

this formula will work if want a column that shows which rows have "D" in it....Just change the "A:A" to "B:B" to the ranges you want....HTH

**bubernak** · 03-26-2013, 10:00 AM

Thanks judgeh59...that worked perfectly! Being new to this forum, I'm not exactly sure what you meant by asking me to add an "*", so if this isn't it...let me know. I really appreciate all the quick responses and sorry I didn't get a chance to update this thread before I had to run to an appoitment yesterday, but I'll defineatly be back when I need some more help!! Thanks, Mike

PS: Think I just figured out what you were talking about with the *.

**judgeh59** · 03-26-2013, 10:03 AM

In the lower left corner of the thread there is a little star....it promotes the reputation of the person....also, at the top of the thread is the Thread tools list....this is where you can change the status to Solved....I'm glad I could help....