Formula Optimizing

slasanka

New Member
Joined
Mar 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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)
)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,211
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Book3.xlsx
IJ
14ABC-1TRUE
15A2B-9FALSE
16Abc-1FALSE
17XYZ-AFALSE
18XYZ-9TRUE
19123-kFALSE
Sheet841
Cell Formulas
RangeFormula
J14:J19J14=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

New Member
Joined
Mar 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Book3.xlsx
IJ
14ABC-1TRUE
15A2B-9FALSE
16Abc-1FALSE
17XYZ-AFALSE
18XYZ-9TRUE
19123-kFALSE
Sheet841
Cell Formulas
RangeFormula
J14:J19J14=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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,807
Messages
5,655,420
Members
418,198
Latest member
jad1984

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top