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? 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. 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? Commented Jun 14, 2021 at 12:55
  • Thanks, for some reason I need to wrap the columns name in square brackets [] Commented Jun 17, 2021 at 6:54
  • Hi mtagliaferri. I'm glad to hear that your problem has been solved. Have a nice day! 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 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. Commented Jun 14, 2021 at 5:33
  • What is the error message? Did you try it again? It is working at my end. 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.