Possible to nest OR() within FIND() and REPLACE()?

[rca]

Hi All,

I have the following formula:

=IF(OR(MID(D3,4,3)="ABC",MID(D3,5,3)="ABC"), REPLACE(D3,FIND("ABC",D3)+4,2,VLOOKUP(MID(D3,FIND("ABC",D3)+4,1),{"H","M";"M","U";"U","Z";"Z","H"},2,FALSE)&MID(D3,FIND("ABC",D3)+5,1)+(MID(D3,FIND("ABC",D3)+4,1)="Z")), IF(OR(MID(D3,4,3)="CAM",MID(D3,5,3)="CAM"),REPLACE(D3,FIND("EMC",D3)+4,2,VLOOKUP(MID(D3,FIND("EMC",D3)+4,1),{"H","M";"M","U";"U","Z";"Z","H"},2,FALSE)&MID(D3,FIND("EMC",D3)+5,1)+(MID(D3,FIND("EMC",D3)+4,1)="Z")), "Error!"))

I'd like to be able to test for another condition within:

REPLACE(D3,FIND("EMC",D3)+4

Ideally, I'd like it to be:

REPLACE(D3,FIND(OR("EMC","HLC"),D3)+4

(testing for "EMC and "HLC")

Is that possible?

Thanks!

 

[pgc01]

Hi


FIND(OR("EMC","HLC"),D3)

In case you find both "EMC" and "HLC" in D3 which result do you want?

 

[rca]

Hi


FIND(OR("EMC","HLC"),D3)

In case you find both "EMC" and "HLC" in D3 which result do you want?

To answer your question, the value in cell D3 will contain either "EMC" or "HLC" (but not both values).

 

[pgc01]

In that case, if the text in D3 contains either "EMC" or "HLC" you can find its postion in the text with:

=LOOKUP(LEN(D3),FIND({"EMC","HLC"},D3))

 

[rca]

In that case, if the text in D3 contains either "EMC" or "HLC" you can find its postion in the text with:

=LOOKUP(LEN(D3),FIND({"EMC","HLC"},D3))

This works perfectly. Thank you.

 

[pgc01]

You're welcome.