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!
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!