A Few Issues!!!

[buddy1000]

A few questions please:

1) In cell A1 I have a word (ex. OEMA)...I am looking for a IF that will lookup a piece of the word. so IF A1 contains EMA....any ideas?

2)In Cell B1 I have a word (ex. Value)...I am looking for a formula in the conditional formatting that will put a grey pattern in cell B2 if B1 is the word Value.


Any ideas please??

 

[baodinh]

The FIND or SEARCH function in Excel will find partial text strings. What is not clear is whether we know what we are looking for in each case or does it change? Post an example spreadsheet and I'm sure we can resolve it.

 

[buddy1000]

Hi Bao....

with respect to 1)
I know what I am looking for:

The words are OEMAM1, ORMAM1 and BARMAM1. These words change often (only the last 2 inputs change (so M1)

the piece of word that uit needs to find are EMA, RMA and RMA...

so say A1 is OEMAM1...then the IF function should only work if the cell contains EMA...

is it If(search(A1,EMa??

any ideas?

 

[baodinh]

Yes, that's right. Assuming A1 contains OEMAM1, B1 contains 999, C1 contains OEMA. In D1, enter the formula =search(C1,A1) which will return a value of 1 corresponding with the first character number of what's found. Note that the formula will return an error if not found.

You can then use conditional formating based on the value in D1.