0

I have a a validation that only accepts a specific format of 6numbers followed by a dash and further 6 numbers as below:

=IF(ISBLANK(FAQ),TRUE,AND(LEN(FAQ)=13,MID(FAQ,7,1)="-",ISNUMBER(MID(FAQ,1,6)+0),LEN(TRIM(MID(FAQ,1,6)))=6,ISNUMBER(MID(FAQ,8,6)+0),LEN(TRIM(MID(FAQ,8,6)))=6))

This works perfectly, however I have to add a further condition that can be entered and this is the phrase 'Not Required'.

Basically the validation will still accept a blank field, a Not required or numbers is the specific format as current formula.

How to I go to add this condition?

Improve this question
2
  • What is the new condition?
    – A.k.A. Fritz
    Commented Jun 13, 2021 at 7:33
  • Your resultant function is likely going to be another IF statement superimposed over the existing function you gave in your original question.
    – A.k.A. Fritz
    Commented Jun 13, 2021 at 7:45

2 Answers 2

Reset to default
0

Please use the below formula:

=OR(ISBLANK(FAQ),FAQ="Not Required",AND(LEN(FAQ)=13,MID(FAQ,7,1)="-",ISNUMBER(MID(FAQ,1,6)+0),LEN(TRIM(MID(FAQ,1,6)))=6,ISNUMBER(MID(FAQ,8,6)+0),LEN(TRIM(MID(FAQ,8,6)))=6))

enter image description here

enter image description here

Improve this answer
3
  • This is the same formula as given in my answer, right?
    – Ganesh Sanap - MVP
    Commented Jun 14, 2021 at 12:55
  • Thanks, for some reason I need to wrap the columns name in square brackets []
    – mtagliaferri
    Commented Jun 17, 2021 at 6:54
  • Hi mtagliaferri. I'm glad to hear that your problem has been solved. Have a nice day!
    – EchoDu_MSFT
    Commented Jun 17, 2021 at 7:46
0

Try this:

=OR(ISBLANK([FAQ]), [FAQ]="Not Required", AND(LEN([FAQ])=13,MID([FAQ],7,1)="-",ISNUMBER(MID([FAQ],1,6)+0),LEN(TRIM(MID([FAQ],1,6)))=6,ISNUMBER(MID([FAQ],8,6)+0),LEN(TRIM(MID([FAQ],8,6)))=6))

More cleaner (same formula):

=OR(ISBLANK(FAQ), FAQ = "Not Required", AND(LEN(FAQ) = 13, MID(FAQ, 7, 1) = "-", ISNUMBER(MID(FAQ, 1, 6) + 0), LEN(TRIM(MID(FAQ, 1, 6))) = 6, ISNUMBER(MID(FAQ, 8, 6) + 0), LEN(TRIM(MID(FAQ, 8, 6))) = 6))
Improve this answer
3
  • I get an error with this code
    – mtagliaferri
    Commented Jun 14, 2021 at 4:33
  • Did you get the syntax error message? I have just tried this with first two conditions in "OR" & it is working fine for me.
    – Ganesh Sanap - MVP
    Commented Jun 14, 2021 at 5:33
  • What is the error message? Did you try it again? It is working at my end.
    – Ganesh Sanap - MVP
    Commented Jun 14, 2021 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.