Partial Text Lookup

[tycasey17]

I am trying to figure out how to detect if a code has a letter in it no matter where in the string of text.

I have a formula giving out codes like this "DR", "RD", "CD", "CR-M", etc. in A1. I am trying to get either "Y" or "N" in A2 if one of the codes contains an "R" in the code. I tried using the LOOKUP function but can't seem to work it right or maybe it's not the best formula to use.

 

[Joe4]

If you are only looking for upper case "R", then try this formula:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"R",""))>0,"Y","N")

If you also want to include lower case "r", use this instead:

=IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R",""))>0,"Y","N")

 

[Automatrix]

Alternative solution. Replace FIND with SEARCH if you need it to be case-insensitive.

=IF(ISNUMBER(FIND("R",A1)),"Y","N")

 

[tycasey17]

All the solutions worked nicely for what I was trying to accomplish, thank you for your assistance.