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

#### rca

##### Board Regular
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!

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### pgc01

##### MrExcel MVP
Hi

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

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

#### rca

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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.

You're welcome.

Replies
6
Views
590
Replies
0
Views
333
Replies
4
Views
314
Replies
8
Views
499
Replies
1
Views
687

1,191,184
Messages
5,985,175
Members
439,945
Latest member
ospe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back