New Contributor

# Formula to find a string

Hello!

I'm struggling with something that seems simple.  I'm trying to wrote a formula that looks for a specific string in a cell:  "Appointment of Rep".  The cell can contain many things, below are a few examples.  I've tried many things but none of them work, any thoughts or ideas!!

Some of the values in the cells, I want to return TRUE if any of the below contain "Appointment of Rep" and FALSE if it does not.

Appointment of Rep

Benefits, Tardiness, Appointment of Rep

Benefits, Appointment of Rep, Tardiness

Benefits, Tardiness

Tardiness, Benefits

5 Replies

# Re: Formula to find a string

Assuming your string is in cell A2, then you may try one of the below formulas...

=IF(ISNUMBER(SEARCH("Appointment of Rep",A2)),"True","False")

OR

=IF(ISNUMBER(FIND("Appointment of Rep",A2)),"True","False")

OR

=IF(COUNTIF(A2,"*Appointment of Rep*"),"True","False")

# Re: Formula to find a string

@Subodh_Tiwari_sktneer Thank you so much!  Those were all of the formulas I had tried and they didn't work, they all returned FALSE.  I even tried those formulas with "*" in between the words as a wild card and it didn't help either.

# Re: Formula to find a string

It would be better if you upload a sample workbook with formulas in place to find the issue with the formulas.

# Re: Formula to find a string

Oddly, when I change the case from TRUE to True and FALSE to False, formulas 3 and 5 work!!!  Thank you so much for your help!!

These two worked for me:

IF(ISNUMBER(SEARCH("Appointment of Rep",A2)),"True","False")

IF(COUNTIF(A2,"*Appointment of Rep*"),"True","False")

 ISSU_DS IF(ISNUMBER(SEARCH("Appointment*Rep",AD,0)),"True","False") IF(ISNUMBER(FIND("Appointment of Rep",AD,0)),"True","False") IF(ISNUMBER(SEARCH("Appointment of Rep",A2)),"True","False") IF(ISNUMBER(FIND("Appointment of Rep",A2)),"True","False") IF(COUNTIF(A2,"*Appointment of Rep*"),"True","False") IF(COUNTIF(A2,"Appointment of Rep"),"TRUE","FALSE") BENEFITS,SPECIALTY CARE,DENIED,CARE/SERVICE AVAILABLE WITHIN PROGRAM False False False False False FALSE BENEFITS,DME,DENIED,DOES NOT MEET CRITERIA False False False False False FALSE ACCESS,SURGERY/PROCEDURE,APPROVED,AUTHORIZATION CONFIRMED False False False False False FALSE BENEFITS,DME,DENIED,DOES NOT MEET CRITERIA False False False False False FALSE BENEFITS,DME,DENIED,NOT COVERED BY BENEFIT False False False False False FALSE BENEFITS,DME,APPROVED,AUTHORIZATION CONFIRMED False False False False False FALSE BENEFITS,RADIOLOGY,DISMISSED,EFFECTUATED PRIOR TO RECEIPT False False False False False FALSE BENEFITS,NUTRITION/DIETICIAN,DENIED,NOT COVERED BY BENEFIT False False False False False FALSE BENEFITS,DME,APPROVED,MEETS CRITERIA False False False False False FALSE BENEFITS,DME,DENIED,DOES NOT MEET CRITERIA False False False False False FALSE BENEFITS,DME,DENIED,CARE/SERVICE NOT MEDICALLY INDICATED False False False False False FALSE BENEFITS,SPECIALTY CARE,DISMISSED,PRE TO POST False False False False False FALSE ACCESS,DME,DENIED,NOT COVERED BY BENEFIT False False False False False FALSE BENEFITS,DME,DENIED,CARE/SERVICE NOT MEDICALLY INDICATED False False False False False FALSE BENEFITS,DME,DENIED,DOES NOT MEET CRITERIA False False False False False FALSE BENEFITS,ALTERNATIVE CARE,APPROVED,AUTHORIZATION CONFIRMED False False False False False FALSE BENEFITS,DME,DENIED,NOT COVERED BY BENEFIT False False False False False FALSE ACCESS,SPECIALTY CARE,APPROVED,MEETS CRITERIA False False False False False FALSE BENEFITS,DME,APPROVED,MEETS CRITERIA False False False False False FALSE BENEFITS,DME,DENIED,CARE/SERVICE NOT MEDICALLY INDICATED False False False False False FALSE BENEFITS,RADIOLOGY,APPROVED,CARE/SERVICE IS MEDICALLY INDICATED False False False False False FALSE QUALITY OF CARE,SECOND OPINION/CONSULT,DENIED,CARE/SERVICE NOT MEDICALLY INDICATED False False False False False FALSE BENEFITS,DME,DISMISSED,NO APPOINTMENT OF REP False False True False True FALSE QUALITY OF CARE,RADIOLOGY,DENIED,CARE/SERVICE NOT MEDICALLY INDICATED False False False False False FALSE BENEFITS,DME,DENIED,NOT COVERED BY BENEFIT False False False False False FALSE BENEFITS,MENTAL HEALTH,DISMISSED,NO APPOINTMENT OF REP False False True False True FALSE

You're welcome!