0

I currently have a column called "MAC Address" - it is not a required field as some pieces of gear do not have an address, but unique values are enforced. If a user puts in an address I want to (at minimum) verify that the format is correct. I currently have the following in the "Column Validation".

=(LEN([MAC Address])=17) +(MID([MAC Address],3,1)=":") +(MID([MAC Address],6,1)=":") +(MID([MAC Address],9,1)=":") +(MID([MAC Address],12,1)=":") +(MID([MAC Address],15,1)=":")=6

However, with every new entry, it flags it as an error because an empty field does not solve this formula. From the pieces I have read online it might be solved with an "OR" or "ISBLANK", but I can't get the right syntax.

Can someone please tell me how to add the additional coding to make this only trigger if the field is NOT blank. Thanks so much!

1

Just add ISBLANK to your formula like this:

=IF(ISBLANK([MAC Address]),6,(LEN([MAC Address])=17) +(MID([MAC Address],3,1)=":") +(MID([MAC Address],6,1)=":") +(MID([MAC Address],9,1)=":") +(MID([MAC Address],12,1)=":") +(MID([MAC Address],15,1)=":"))=6

UPD: or

=OR(ISBLANK([MAC Address]),AND(LEN([MAC Address])=17,MID([MAC Address],3,1)=":",MID([MAC Address],6,1)=":",MID([MAC Address],9,1)=":",MID([MAC Address],12,1)=":",MID([MAC Address],15,1)=":"))
  • This worked!!! Thank you. Can you explain why you used the syntax you did. Specifically the ,6, – Chris Plonka Jun 28 '18 at 1:41
  • If your field is blank then you need to place 6 - the correct sum for the else variant. If you'll change conditions you should change this value in both places in the formula. Or you can use another variant - with OR abd AND function. I just added it to answer. – AlexZh Jun 28 '18 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.