Jun 21 2022 06:12 PM
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
Thank you for your help in advance!!
Jun 21 2022 07:50 PM
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")
Jun 22 2022 09:20 AM
@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.
Jun 22 2022 10:02 AM
It would be better if you upload a sample workbook with formulas in place to find the issue with the formulas.
Jun 22 2022 10:19 AM
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 |