Avatar of route217
route217
Flag for United Kingdom of Great Britain and Northern Ireland asked on

modify current formula to add additional step part 2

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.
Microsoft Excel

Avatar of undefined
Last Comment
Rgonzo1971

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
route217

ASKER
Rgonzo 1971...
Formula not working...no end result
Rgonzo1971

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck