modify  current formula to add additional step part 2

route217
route217 used Ask the Experts™
on
Hi Experts

I need to add to the following formula =IF(AND(OR(LEN(C2)=8,LEN(C2)=7),OR(ISNUMBER(SEARCH("o",LEFT(C2,1),1)),ISNUMBER(SEARCH("e",LEFT(C2,1),1)))),SUBSTITUTE(C2,C2,"-"),C2)

One more step...
If cell c2 has BAV(o123456 from the right then remove and leave BAV only..incorporate into above formula.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2016
Commented:
Hi,

pls try

=IF(AND(OR(LEN(C2)=8,LEN(C2)=7),OR(ISNUMBER(SEARCH("o",LEFT(C2,1),1)),ISNUMBER(SEARCH("e",LEFT(C2,1),1)))),SUBSTITUTE(C2,C2,"-"),IF(LEFT(C2,4)="BAV(","BAV",C2))

Open in new window

regards
route217Junior

Author

Commented:
Rgonzo 1971...
Formula not working...no end result
Rgonzo1971
Top Expert 2016

Commented:
then try

=IF(AND(OR(LEN(C2)=8,LEN(C2)=7),OR(ISNUMBER(SEARCH("o",LEFT(C2,1),1)),ISNUMBER(SEARCH("e",LEFT(C2,1),1)))),SUBSTITUTE(C2,C2,"-"),IF(AND(LEFT(C2,4)="BAV(",OR(LEN(C2)=12,LEN(C2)=11),OR(ISNUMBER(SEARCH("o",MID(C2,5,1),1)),ISNUMBER(SEARCH("e",MID(C2,5,1),1)))),SUBSTITUTE(C2,C2,"BAV"),C2))

Open in new window


EDIT corrected code

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial