Formula Optimizing

[slasanka]

I have create a custom formula , but some part of mine telling me this is ******** and there is very easy way to do the same thing that I don't know , can someone tell me if there is a way to optimize my this formula plz.

AND(
OR(
MID($I$14,1,1)="A",MID($I$14,1,1)="B",MID($I$14,1,1)="C",MID($I$14,1,1)="D",MID($I$14,1,1)="E",MID($I$14,1,1)="F",MID($I$14,1,1)="G",MID($I$14,1,1)="H",MID($I$14,1,1)="I",
MID($I$14,1,1)="J",MID($I$14,1,1)="K",MID($I$14,1,1)="L",MID($I$14,1,1)="M",MID($I$14,1,1)="N",MID($I$14,1,1)="O",MID($I$14,1,1)="P",MID($I$14,1,1)="Q",MID($I$14,1,1)="R",
MID($I$14,1,1)="S",MID($I$14,1,1)="T",MID($I$14,1,1)="U",MID($I$14,1,1)="V",MID($I$14,1,1)="W",MID($I$14,1,1)="X",MID($I$14,1,1)="Y",MID($I$14,1,1)="Z"
)
,
OR(
MID($I$14,2,1)="A",MID($I$14,2,1)="B",MID($I$14,2,1)="C",MID($I$14,2,1)="D",MID($I$14,2,1)="E",MID($I$14,2,1)="F",MID($I$14,2,1)="G",MID($I$14,2,1)="H",MID($I$14,2,1)="I",
MID($I$14,2,1)="J",MID($I$14,2,1)="K",MID($I$14,2,1)="L",MID($I$14,2,1)="M",MID($I$14,2,1)="N",MID($I$14,2,1)="O",MID($I$14,2,1)="P",MID($I$14,2,1)="Q",MID($I$14,2,1)="R",
MID($I$14,2,1)="S",MID($I$14,2,1)="T",MID($I$14,2,1)="U",MID($I$14,2,1)="V",MID($I$14,2,1)="W",MID($I$14,2,1)="X",MID($I$14,2,1)="Y",MID($I$14,2,1)="Z"
)
,
OR(
MID($I$14,3,1)="A",MID($I$14,3,1)="B",MID($I$14,3,1)="C",MID($I$14,3,1)="D",MID($I$14,3,1)="E",MID($I$14,3,1)="F",MID($I$14,3,1)="G",MID($I$14,3,1)="H",MID($I$14,3,1)="I",
MID($I$14,3,1)="J",MID($I$14,3,1)="K",MID($I$14,3,1)="L",MID($I$14,3,1)="M",MID($I$14,3,1)="N",MID($I$14,3,1)="O",MID($I$14,3,1)="P",MID($I$14,3,1)="Q",MID($I$14,3,1)="R",
MID($I$14,3,1)="S",MID($I$14,3,1)="T",MID($I$14,3,1)="U",MID($I$14,3,1)="V",MID($I$14,3,1)="W",MID($I$14,3,1)="X",MID($I$14,3,1)="Y",MID($I$14,3,1)="Z"
)
,
MID($I$14,4,1)="-"
,
OR(
MID($I$14,5,1)="0",MID($I$14,5,1)="1"MID($I$14,5,1)="2"MID($I$14,5,1)="3"MID($I$14,5,1)="4"MID($I$14,5,1)="5"MID($I$14,5,1)="6"MID($I$14,5,1)="7"MID($I$14,5,1)="8"
MID($I$14,5,1)="9")
,
LEN($I$14=5)
)

 

[Fluff]

It would help if you could a sample of your data & explain what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jtakw]

Hi,

Maybe,
Assuming you want the first 3 characters Case-Sensitive, if Not, change FIND to SEARCH within the formula:

Book3.xlsx
	I	J
14	ABC-1	TRUE
15	A2B-9	FALSE
16	Abc-1	FALSE
17	XYZ-A	FALSE
18	XYZ-9	TRUE
19	123-k	FALSE
Sheet841

Cell Formulas
Range	Formula
J14:J19	J14	=IFERROR(AND(SUM(SIGN(FIND(MID(I14,{1,2,3},1),{"ABCDEFGHIJKLMNOPQRSTUVWXYZ"})))=3,MID(I14,4,1)="-",ISNUMBER(RIGHT(I14)+0),LEN(I14)=5),FALSE)

 

[slasanka]

Hi,

Maybe,
Assuming you want the first 3 characters Case-Sensitive, if Not, change FIND to SEARCH within the formula:

Book3.xlsx
	I	J
14	ABC-1	TRUE
15	A2B-9	FALSE
16	Abc-1	FALSE
17	XYZ-A	FALSE
18	XYZ-9	TRUE
19	123-k	FALSE
Sheet841

Cell Formulas
Range	Formula
J14:J19	J14	=IFERROR(AND(SUM(SIGN(FIND(MID(I14,{1,2,3},1),{"ABCDEFGHIJKLMNOPQRSTUVWXYZ"})))=3,MID(I14,4,1)="-",ISNUMBER(RIGHT(I14)+0),LEN(I14)=5),FALSE)

Wow that's grade that's what exactly just i wanted to do. ill look in to it and ill learn it and thank you very much for your time and help

 

[jtakw]

You're welcome, thanks for the feedback.