Extract Data from Single Cell based on multiple conditions

[mohdamir1989]

Dear All,

I have single cell having remarks of work done. I need to extract the data based on two conditions. I'm already using below formula which returns the word in cell but not fulfilling the condition I need.


Relay & Capacitor replaced

=IF(ISNUMBER(SEARCH("Relay",$T64)), "Relay", "")

It returns the relay, But I need it to output "relay" only if "replaced" is there in cell. as sometimes string is as below
Relay & Capacitor adjusted/missing

in such case it should not pick up "relay"

Your help in this regards is highly appreciated

Best Regards
M. Amir Waqas

 

[pkdeimos]

Hi,

You can your additional condition within if..

=IF(AND(ISNUMBER(SEARCH("Relay",$T64)), ISNUMBER(SEARCH("replaced",$T64))), "Relay", "")

Hope it works.

 

[mohdamir1989]

Hi,

You can your additional condition within if..

=IF(AND(ISNUMBER(SEARCH("Relay",$T64)), ISNUMBER(SEARCH("replaced",$T64))), "Relay", "")

Hope it works.

It is working perfectly. Thank so very much for kind and prompt response. Really appreciated.

I have number of such items in sheet on which implementing formula may slower down the performance of file. Is there any way I may implement this via VBA on day end and it paste the values and saves?

Best Regards

 

[thisoldman]

This might be a better formula if you are looking for performance—fewer function calls and no IF():

=REPT("Relay",--ISNUMBER(SEARCH("Relay",$T64)*SEARCH("replaced",$T64)))

 

[mohdamir1989]

This might be a better formula if you are looking for performance—fewer function calls and no IF():

=REPT("Relay",--ISNUMBER(SEARCH("Relay",$T64)*SEARCH("replaced",$T64)))

Thank you for simplifying it little furter. My file is of 25MBs now and I am using Excel 2007 on coure 2 duo machine which stucks many times.

I am looking for VBA which upon status change may run formula and paste values.

Note: I have no idea of VBA at all