Excel formula on cell for valid password

user_15555713 0
Viewed 80

I'm creating a template for importing users in bulk to the system, one of the columns requires to input password,

I would like to create a condition on the [password] cell, in order to indicate to the person that input the details that the password is valid

those are the conditions:

Password must contain at least

6 characters

1 symbol

1 number

1 letter

This is what I tried : MEDIAN(6) AND (OR),EXACT(LOWER()))) but no luck. all the symbols are valid but the values of the characters must be in English

is it possible?

excel-formula validation passwords excel
2 Answers

A password-validator immediately screamed 'regular-expressions'. Will you want to go down the path of VBA, you'd require the following pattern:

^(?=.*?[!#$])(?=.*?[A-Za-z])(?=.*?\d).{6,}$

See an online demo

To mimic this in relative easy native functionality you could go with xpath-expressions:

=NOT(ISERROR(FILTERXML("<t><s>"&LOWER(A1)&"</s></t>","//s[translate(.,'abcdefghijklmnopqrstuvwxyz','')!=.][translate(.,'0123456789','')!=.][translate(.,'!#$','')!=.][string-length()>=6]")))
  • translate(.,'abcdefghijklmnopqrstuvwxyz','')!=.] would mimic the positive lookahead for any letter;
  • [translate(.,'0123456789','')!=.] would mimic the positive lookahead for any number;
  • [translate(.,'!#$','')!=.] would mimic the positive lookahead for any symbol (as given in the character class);
  • [string-length()>=6] would mimic the 6+ characters needed for valid input.
user_97581940

Well, I have done this in the few minutes between posting the comment and this answer, you can expand it to include whatever other conditions you want:

enter image description here

and(len(A1)>=6,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1,1),"")))

is the formula used in the data validation. Youi can see that A1 meets the length >=6 but I have not controlled that the count of numbers, hiowever you can add that.

user_49617000
Related