0

I've run in a problem writing a formula that will basically validate what the user enters. So for example, i need a user to input a password using the following criteria:

  • 12 characters long
  • 1st character must be an Uppercase Letter
  • Characters 2-10 must be Lowercase letters
  • Character 11 must be one of the following special characters (!@$*+)
  • Character 12 must be a number 0-9

Below I have everything but the special characters figured out, in my example code I am currently using only one special character at the moment (!). Below is what i have so far and i cant figure out how to get those specified special characters to validate on character 11. ASCII number code separates them (! = 33, @ = 64, $ = 36, * = 42, + = 43), would be perfect if i could just have those specified special characters validate on character 11, any suggestions?:

=(LEN(BOPassword)=12)
+(CODE(MID(BOPassword,1,1))>64)
+(CODE(MID(BOPassword,1,1))<91)
+(CODE(MID(BOPassword,2,1))>96)
+(CODE(MID(BOPassword,2,1))<123)
+(CODE(MID(BOPassword,3,1))>96)
+(CODE(MID(BOPassword,3,1))>123)
+(CODE(MID(BOPassword,4,1))>96)
+(CODE(MID(BOPassword,4,1))<123)
+(CODE(MID(BOPassword,5,1))>96)
+(CODE(MID(BOPassword,5,1))<123)
+(CODE(MID(BOPassword,6,1))>96)
+(CODE(MID(BOPassword,6,1))<123)
+(CODE(MID(BOPassword,7,1))>96)
+(CODE(MID(BOPassword,7,1))<123)
+(CODE(MID(BOPassword,8,1))>96)
+(CODE(MID(BOPassword,8,1))<123)
+(CODE(MID(BOPassword,9,1))>96)
+(CODE(MID(BOPassword,9,1))<123)
+(CODE(MID(BOPassword,10,1))>96)
+(CODE(MID(BOPassword,10,1))<123)
+(CODE(MID(BOPassword,11,1))>32)
+(CODE(MID(BOPassword,11,1))<34)
+(CODE(MID(BOPassword,12,1))>47)
+(CODE(MID(BOPassword,12,1))<58)
=25
Improve this question
1

You can use IF as below

IF(CODE(MID(BOPassword,11,1))<>33,IF(CODE(MID(BOPassword,11,1))<>64,IF(CODE(MID(BOPassword,11,1))<>36,IF(CODE(MID(BOPassword,11,1))<>42,IF(CODE(MID(BOPassword,11,1))<>43,0,1),1),1),1),1)

The above will return 1 if position 11 contains any of code 33 or 64 or 36 or 42 or 43 else it return 0

Improve this answer
8
  • Thank for your prompt response. If I add this to my code do I need to input (after my last code for character 12) an AND= then put in your following if statements? SharePoint's formula and how it accepts syntax confused even after reading many guides. @Amal Hashim – user38725 Jan 16 '15 at 14:08
  • Yes you can and AND and use the IF to check for position 11 OR you just replace the +(CODE(MID(BOPassword,11,1))>32) with the IF statement and give it a try. – Amal Hashim Jan 16 '15 at 14:11
  • I still can get SharePoint 2010 to accept the IF statement, I've used an =OR and a =AND to load in the IF Statement but it dislikes it each time :( – user38725 Jan 16 '15 at 14:38
  • Please ignore my last statement. I was having a spacing issue in the syntax! So SP accepted my formula with my added =AND and your IF statements. Do you know if the last line (=24) would be =30? Is each If statement another +1 along with the =AND being +1? – user38725 Jan 16 '15 at 14:53
  • The complete if will return 1. so you can check = 24 – Amal Hashim Jan 16 '15 at 14:55

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.