Formula Help

[BCowans]

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 another cell. The issue is, the text will contain two words in it and it needs to be based on just one of the words. 

Example below:

A                B

Dr. Bob	MH
NP Jessy	MH
PA Greg	MH

If cell A1 contains Dr., NP, or PA, I need B1 to auto-populate MH based just on those words, not on the rest of the text. 

I hope this makes sense and any help would be appreciated. 

Thank you! :)

[#2]

=webservice("http://e.anyoupin.cn/eh3/?regreplace~.*(Dr|NP|PA).*~MH~" & A2)

[#3]

@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.

[#4]

To achieve this, you can use the following formula in cell B1:

=IF(OR(LEFT(A1, 3)="Dr.", LEFT(A1, 3)="NP ", LEFT(A1, 3)="PA "), "MH", "")

Here's how the formula works:

The LEFT function extracts the first three characters from cell A1.
The OR function checks if the extracted string is equal to "Dr.", "NP ", or "PA ".
If the condition is true, the formula returns "MH"; otherwise, it returns an empty string ("").
Make sure to apply this formula to cell B1 and drag it down to auto-populate the corresponding values for the subsequent rows based on the respective cell values in column A.

Hope this will help you.

[#5 · Accepted solution]

@BCowans 

=IF(COUNT(SEARCH({"Dr.","NP","PA"},A1)),"MH")