1

I am using SharePoint 2016 and I am trying to enter a validation for a single line text that must be 7 characters in length with a hyphen in position for (XXX-XXX).

I have two issue, first I released that if I enter a validation it makes the column mandatory so I need to make the column have the option of blank and if not blank then the validation sets in.

The second issue is that the formula does not really work properly as it appears. I can have 2 hyphen and the last characters not capitalized.

=AND(LEN(Route)=7,MID(Route,4,1)="-",CODE(UPPER(MID(Route,1,1)))=CODE(MID(Route,1,1)),CODE(UPPER(MID(Route,2,1)))=CODE(MID(Route,2,1)),CODE(UPPER(MID(Route,3,1)))=CODE(MID(Route,3,1)),CODE(UPPER(MID(Route,4,1)))=CODE(MID(Route,4,1)),CODE(UPPER(MID(Route,5,1)))=CODE(MID(Route,5,1)))

As soon as I enter the 6th character condition as below SharePoint goes into error without saving the setting.

=AND(LEN(Route)=7,MID(Route,4,1)="-",CODE(UPPER(MID(Route,1,1)))=CODE(MID(Route,1,1)),CODE(UPPER(MID(Route,2,1)))=CODE(MID(Route,2,1)),CODE(UPPER(MID(Route,3,1)))=CODE(MID(Route,3,1)),CODE(UPPER(MID(Route,4,1)))=CODE(MID(Route,4,1)),CODE(UPPER(MID(Route,5,1)))=CODE(MID(Route,5,1))),CODE(UPPER(MID(Route,6,1)))=CODE(MID(Route,6,1)))

Any ideas on how to make the entry optional and if information is entered then to be in the format XXX-XXX.

0

You can try this formula:

=IF(ISBLANK(Route),TRUE,AND(LEN(Route)=7,NOT(MID(Route,5,1)="-"),NOT(MID(Route,6,1)="-"),NOT(MID(Route,7,1)="-"),MID(Route,4,1)="-",CODE(UPPER(MID(Route,1,1)))=CODE(MID(Route,1,1)),CODE(UPPER(MID(Route,2,1)))=CODE(MID(Route,2,1)),CODE(UPPER(MID(Route,3,1)))=CODE(MID(Route,3,1)),CODE(UPPER(MID(Route,5,1)))=CODE(MID(Route,5,1)),CODE(UPPER(MID(Route,6,1)))=CODE(MID(Route,6,1)),CODE(UPPER(MID(Route,7,1)))=CODE(MID(Route,7,1))))

I have added extra checks for the misplaced "-" at 5,6 and 7th position:

    NOT(MID(Route,5,1)="-"),
    NOT(MID(Route,6,1)="-"),
    NOT(MID(Route,7,1)="-")

Results

enter image description here

enter image description here

1
  • Thanks this has taken care of the problem. really apreciated the help – mtagliaferri Apr 20 at 19:59
1

Looks like there is one additional ) in your 2nd formula. It should be like:

=AND(LEN(Route)=7,MID(Route,4,1)="-",CODE(UPPER(MID(Route,1,1)))=CODE(MID(Route,1,1)),CODE(UPPER(MID(Route,2,1)))=CODE(MID(Route,2,1)),CODE(UPPER(MID(Route,3,1)))=CODE(MID(Route,3,1)),CODE(UPPER(MID(Route,4,1)))=CODE(MID(Route,4,1)),CODE(UPPER(MID(Route,5,1)))=CODE(MID(Route,5,1)),CODE(UPPER(MID(Route,6,1)))=CODE(MID(Route,6,1)))

Also, you can check if the column is blank or not & set column validation based on it like:

=IF(ISBLANK([Route]),TRUE,FALSE)

After combining above two formulas, final formula should be like:

=IF(ISBLANK([Route]),TRUE,AND(LEN(Route)=7,MID(Route,4,1)="-",CODE(UPPER(MID(Route,1,1)))=CODE(MID(Route,1,1)),CODE(UPPER(MID(Route,2,1)))=CODE(MID(Route,2,1)),CODE(UPPER(MID(Route,3,1)))=CODE(MID(Route,3,1)),CODE(UPPER(MID(Route,4,1)))=CODE(MID(Route,4,1)),CODE(UPPER(MID(Route,5,1)))=CODE(MID(Route,5,1)),CODE(UPPER(MID(Route,6,1)))=CODE(MID(Route,6,1))))
2
  • Thanks for the help! It now allow me to have a blank column but the format is still not right, I have changed the formula to check character 123 and 567 which is working for all capital letters, but I am able to have hyphens from position 4. (e.g. AAA---- or AAA--AA or AAA---A) How do I go by having hyphen only in position 4 – mtagliaferri Apr 18 at 6:45
  • I used =IF(ISBLANK(Route),TRUE,AND(LEN(Route)=7,MID(Route,4,1)="-",CODE(UPPER(MID(Route,1,1)))=CODE(MID(Route,1,1)),CODE(UPPER(MID(Route,2,1)))=CODE(MID(Route,2,1)),CODE(UPPER(MID(Route,3,1)))=CODE(MID(Route,3,1)),CODE(UPPER(MID(Route,5,1)))=CODE(MID(Route,5,1)),CODE(UPPER(MID(Route,6,1)))=CODE(MID(Route,6,1)),CODE(UPPER(MID(Route,7,1)))=CODE(MID(Route,7,1)))) – mtagliaferri Apr 18 at 6:48

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.