Forum Discussion

BCowans's avatar
BCowans
Copper Contributor
Jul 14, 2023
Solved

Formula Help

Hello everyone! I need help with what I imagine could be a simple formula but my brain is having a hard time figuring it out.  I need a formula that auto-populates a text in another cell based on a...
excel
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jul 15, 2023

    BCowans 

    =IF(COUNT(SEARCH({"Dr.","NP","PA"},A1)),"MH")
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jul 15, 2023

BCowans 

To auto-populate the text "MH" in cell B1 based on the presence of specific words in cell A1, you can use an IF statement with the SEARCH function in Excel.

Here is the formula you can use in cell B1:

=IF(OR(ISNUMBER(SEARCH("Dr.", A1)), ISNUMBER(SEARCH("NP", A1)), ISNUMBER(SEARCH("PA", A1))), "MH", "")

This formula checks if the cell A1 contains any of the specified words ("Dr.", "NP", "PA") using the SEARCH function. If any of the words are found, it returns "MH" in cell B1. If none of the words are found, it returns an empty string ("").

Simply copy this formula to other cells in column B to apply the same logic to other rows.

Note that the formula is case-sensitive, so it will only detect the specified words exactly as written. If you want it to be case-insensitive, you can use the UPPER or LOWER function to convert the text in cell A1 and the search terms to the same case before comparing them. The text and the steps are the result of various AI's put together.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Resources