Return specific text if a cell contains specific text if not return blank

[ecrodrig]

I am trying to return a specific text on one cell based on the text from another cell. Example If cell P3 includes OTC then return blank on cell d3 if it includes anything other than OTC (n/a, tbd) then return N/A in cell D3. I also want to know if it includes Cell P3 includes "OTC & T&M" then leave blank on cell C3 if there is anything else (n/a, Tbd) the return N/A on cell C3

 

[Fluff]

Hi & welcome to MrExcel.
How about

=if(isnumber(search("OTC",P3)),"","N/A")

 

[ecrodrig]

Hi & welcome to MrExcel.
How about

=if(isnumber(search("OTC",P3)),"","N/A")

THANKS!! So that works but if I wanted to search for more words within the same cell do I just add a coma and the word inside quotes?

 

[Fluff]

You can do that like

=IF(MAX(ISNUMBER(SEARCH({"OTC","T&M"},P3))),"","N/A")

How many words do you need to search for?

 

[ecrodrig]

You can do that like

=IF(MAX(ISNUMBER(SEARCH({"OTC","T&M"},P3))),"","N/A")

How many words do you need to search for?

So let’s see basically I have 3 columns one is for t&m one for otc and one that will show if it is otc or t&m or both or it may also be n/a or tbd those are my options so if Column p3 (the one that could have otc, t&m, both, n/a or tbd) Has just t&m then column c3 (t&m column) would be blank and column d3 (otc column) would say n/a but if it column p3 has otc and t&m then both thw otc and t&m columns should stay blank. Tbds should stay blank on either column and n/a should be n/a does that make sense?

 

[ecrodrig]

So let’s see basically I have 3 columns one is for t&m one for otc and one that will show if it is otc or t&m or both or it may also be n/a or tbd those are my options so if Column p3 (the one that could have otc, t&m, both, n/a or tbd) Has just t&m then column c3 (t&m column) would be blank and column d3 (otc column) would say n/a but if it column p3 has otc and t&m then both thw otc and t&m columns should stay blank. Tbds should stay blank on either column and n/a should be n/a does that make sense?

Btw, that last formula didn’t work it’s giving me an n/a although my column has both otc and t&m so it should have been blank

 

[Fluff]

does that make sense?

Unfortunately not.
Can you post some sample data showing expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ecrodrig]

Unfortunately not.
Can you post some sample data showing expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Yes will post sample once I am in front of pc I am on my cell now thanks so much for trying to help as soon as I can I will post the sample

 

[ecrodrig]

I figured out a way to do it:

=IF(OR($P3=“T&M, $P4=“n/a”),”N/A”,””)

With the above any text that is not t&m or n/a will give me a blank and any text that is will state N/A

 

[Fluff]

Glad you sorted it & thanks for the feedback.