This is Excel question,
这是Excel问题,
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:
这些是条件:
Passwords must be between 8 and 20 characters.
Must contain one lower & uppercase letter,
and one non-alpha character (a number or a symbol.)
is it possible?
可能吗?
2 个解决方案
#1
3
Let's break it down into stages:
让我们把它分解成几个阶段:
Passwords must be between 8 and 20 characters.
密码必须介于8到20个字符之间。
So, this means that for the value in cell A1, we want where LEN(A1) is >=8 and <=20. There are a couple of ways to do this, I'll go with MEDIAN(8, LEN(A1), 20)=LEN(A1) as a simple one.
因此,这意味着对于单元格A1中的值,我们希望LEN(A1)> = 8且<= 20。有几种方法可以做到这一点,我将使用MEDIAN(8,LEN(A1),20)= LEN(A1)作为一个简单的方法。
Must contain one lower & uppercase letter
必须包含一个小写和大写字母
Now, if you just run a check on UPPER(A1)=LOWER(A1) then you see that a normal check ignores Case... But, an EXACT comparison preserves case! So, EXACT(A1,UPPER(A1)) will be TRUE if all the letters in cell A1 are capital. We want this to be FALSE for both the UPPER and LOWER checks: NOT(OR(EXACT(A1,UPPER(A1)),EXACT(A1,LOWER(A1))))
现在,如果您只是检查UPPER(A1)= LOWER(A1),那么您会看到正常检查忽略Case ...但是,一个EXACT比较保留了大小写!因此,如果单元格A1中的所有字母都是大写字母,则EXACT(A1,UPPER(A1))将为TRUE。我们希望UPPER和LOWER检查都为FALSE:NOT(OR(EXACT(A1,UPPER(A1)),EXACT(A1,LOWER(A1))))
and one non-alpha character (a number or a symbol.)
和一个非字母字符(数字或符号。)
This is the tricky one. I'm going to recommend using SUMPRODUCT to force an array-formula calculation of an AGGREGATE to FIND one of a specific list of symbols. For example, if you were looking for any of "!", "£" or "$" then this code would return TRUE if they were present, or FALSE if they were not: IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND({"!","£","$"},A1),1)),0)>0
这是棘手的。我将建议使用SUMPRODUCT强制对AGGREGATE进行数组公式计算以查找特定符号列表之一。例如,如果您要查找“!”,“£”或“$”中的任何一个,那么此代码如果存在则返回TRUE,如果不存在则返回FALSE:IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND) ({ “!”, “£”, “$”},A1),1)),0)> 0
If you can use a hidden sheet, you can put your "symbols" into a column to check, for example: IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND(SuperHiddenSheet!$A$1:$A$50,1)),0)>0
如果你可以使用隐藏的工作表,你可以将你的“符号”放入一列进行检查,例如:IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND(SuperHiddenSheet!$ A $ 1:$ A $ 50,1)), 0)> 0
Now, just stick your 3 conditions into an AND, and you have your test:
现在,只需将您的3个条件粘贴到AND中,即可进行测试:
=AND(MEDIAN(8, LEN(A1), 20)=LEN(A1), NOT(OR(EXACT(A1,UPPER(A1)),EXACT(A1,LOWER(A1)))), IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND({"0","1","2","3","4","5","6","7","8","9","!","£","$","%","^","&","(",")","@","'","~","#","\","/","|","<",">","[","]","{","}","_","-","+","="},A1),1)),0)>0)
(I have left "*" out of the "special character" list)
(我从“特殊字符”列表中留下了“*”)
#2
0
I don't think there's any way to find any symbol in formulas, VBA might work better. Please check if this is good enough for you.
我认为没有办法在公式中找到任何符号,VBA可能会更好。请检查这是否足够好。
=IF(AND(NOT(EXACT(UPPER(A1);A1));NOT(EXACT(LOWER(A1);A1));LEN(A1)<=20;LEN(A1)>=8;OR(COUNT(FIND({0;1;2;3;4;5;6;7;8;9};A1))>0;(COUNT(FIND({"`";"~";"!";"@";"#";"$";"%";"^";"&";"*";"(";")";"_";"-";"+";"=";"{";"}";"[";"]";"\";"|";":";";";"""";"'";"<";">";",";".";"?";"/"};A1)))>0));"VALID";"INVALID")
#1
3
Let's break it down into stages:
让我们把它分解成几个阶段:
Passwords must be between 8 and 20 characters.
密码必须介于8到20个字符之间。
So, this means that for the value in cell A1, we want where LEN(A1) is >=8 and <=20. There are a couple of ways to do this, I'll go with MEDIAN(8, LEN(A1), 20)=LEN(A1) as a simple one.
因此,这意味着对于单元格A1中的值,我们希望LEN(A1)> = 8且<= 20。有几种方法可以做到这一点,我将使用MEDIAN(8,LEN(A1),20)= LEN(A1)作为一个简单的方法。
Must contain one lower & uppercase letter
必须包含一个小写和大写字母
Now, if you just run a check on UPPER(A1)=LOWER(A1) then you see that a normal check ignores Case... But, an EXACT comparison preserves case! So, EXACT(A1,UPPER(A1)) will be TRUE if all the letters in cell A1 are capital. We want this to be FALSE for both the UPPER and LOWER checks: NOT(OR(EXACT(A1,UPPER(A1)),EXACT(A1,LOWER(A1))))
现在,如果您只是检查UPPER(A1)= LOWER(A1),那么您会看到正常检查忽略Case ...但是,一个EXACT比较保留了大小写!因此,如果单元格A1中的所有字母都是大写字母,则EXACT(A1,UPPER(A1))将为TRUE。我们希望UPPER和LOWER检查都为FALSE:NOT(OR(EXACT(A1,UPPER(A1)),EXACT(A1,LOWER(A1))))
and one non-alpha character (a number or a symbol.)
和一个非字母字符(数字或符号。)
This is the tricky one. I'm going to recommend using SUMPRODUCT to force an array-formula calculation of an AGGREGATE to FIND one of a specific list of symbols. For example, if you were looking for any of "!", "£" or "$" then this code would return TRUE if they were present, or FALSE if they were not: IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND({"!","£","$"},A1),1)),0)>0
这是棘手的。我将建议使用SUMPRODUCT强制对AGGREGATE进行数组公式计算以查找特定符号列表之一。例如,如果您要查找“!”,“£”或“$”中的任何一个,那么此代码如果存在则返回TRUE,如果不存在则返回FALSE:IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND) ({ “!”, “£”, “$”},A1),1)),0)> 0
If you can use a hidden sheet, you can put your "symbols" into a column to check, for example: IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND(SuperHiddenSheet!$A$1:$A$50,1)),0)>0
如果你可以使用隐藏的工作表,你可以将你的“符号”放入一列进行检查,例如:IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND(SuperHiddenSheet!$ A $ 1:$ A $ 50,1)), 0)> 0
Now, just stick your 3 conditions into an AND, and you have your test:
现在,只需将您的3个条件粘贴到AND中,即可进行测试:
=AND(MEDIAN(8, LEN(A1), 20)=LEN(A1), NOT(OR(EXACT(A1,UPPER(A1)),EXACT(A1,LOWER(A1)))), IFERROR(SUMPRODUCT(AGGREGATE(15,6,FIND({"0","1","2","3","4","5","6","7","8","9","!","£","$","%","^","&","(",")","@","'","~","#","\","/","|","<",">","[","]","{","}","_","-","+","="},A1),1)),0)>0)
(I have left "*" out of the "special character" list)
(我从“特殊字符”列表中留下了“*”)
#2
0
I don't think there's any way to find any symbol in formulas, VBA might work better. Please check if this is good enough for you.
我认为没有办法在公式中找到任何符号,VBA可能会更好。请检查这是否足够好。
=IF(AND(NOT(EXACT(UPPER(A1);A1));NOT(EXACT(LOWER(A1);A1));LEN(A1)<=20;LEN(A1)>=8;OR(COUNT(FIND({0;1;2;3;4;5;6;7;8;9};A1))>0;(COUNT(FIND({"`";"~";"!";"@";"#";"$";"%";"^";"&";"*";"(";")";"_";"-";"+";"=";"{";"}";"[";"]";"\";"|";":";";";"""";"'";"<";">";",";".";"?";"/"};A1)))>0));"VALID";"INVALID")