Excel Formula or VBA to find cell contains a certain text

[sprimax]

Hi Folks,

Let's say I have these words in Column A (A1:A4)
Green Apple
Red Apple
Yellow Banana Cute
Green Banana

How is the code or formula be written if I want to get Apple & Banana return in B1:B4?
B1 = Apple
B2 = Apple
B3 = Banana
B4 = Banana

Thanks so much

 

[murrayty]

Are Apple and Banana going to be your only choices and will they ever both occur? Will there be times where neither occur?

 

[sprimax]

Dear Murrayty,

This is the fact, I have more than 7 criteria to find in column O (15), they are :
Partial
Surplus
Discrepancy
Waiting Memo
Waiting Physical
Inventory On Progress
Prepare Physical Summary

Thanks

 

[murrayty]

And they will be showing up along with other miscellaneous text in the same cell?

 

[sprimax]

Dear Murrayty,

These are the actual sentences in the column
- Partial Close Out (desired return = Partial)
- Waiting Memo Close Out (desired return = Waiting Memo)
- Material Surplus (desired return = Material Surplus)
- Material Surplus & Outstanding Warehouse (desired return = Material Surplus)
- Discrepancy between material installed (desired return = Discrepancy)
- Prepare Discrepancy (desired return = Discrepancy)
- Waiting Physical Inventory (desired return = Waiting Physical)
- Prepare Summary (desired return = Prepare Summary)

Thank you

 

[murrayty]

I would just make a lookup table on another sheet and call to it.

=VLOOKUP(A1,Sheet2!$A$1:$B$8,2,FALSE)

A1 = The cell you want to lookup

Sheet2 A1:B8 =

Discrepancy between material installed	        Discrepancy
Material Surplus	                        Material Surplus
Material Surplus & Outstanding Warehouse	Material Surplus
Partial Close Out	                        Partial
Prepare Discrepancy	                        Discrepancy
Prepare Summary	                                Prepare Summary
Waiting Memo Close Out	                        Waiting Memo
Waiting Physical Inventory	                Waiting Physical

It might be possible to write a formula to do it but I am not having any luck myself.

 

[sprimax]

Dear Murrayty,

Thanks for your time. I will take the option if I can't get another formula or VBA code.

Again. thank you very much