I've just started taking an Excel course, and I've been getting along ok until now. I needed to figure out a formula to count the number of characters in cells, which I figured out, but I'm stuck on figuring out a formula that will look at cells, for example, D4 through D7, to see if there are uppercase letters, lowercase letters, numbers, or special characters. If there are uppercase, lowercase, etc, I want a cell to display a value, for example "1." My instructor recommended using the IF and COUNT functions, but I can't seem to figure out how they'd accomplish what I'm trying to do. Any help would be greatly appreciated.
I have attached an except of some of the examples I'm trying to work out. Essentially, if there is a uppercase in a password (I need to check multiple cells, not just one), I need to assign the Uppercase Letters with a 2. If there isn't, I need to assign it with a 0.
This would be repeated with the lowercase numbers, numbers, and special characters respectively. For length, I need to count the amount of characters in the passwords (multiple cells again, not just one), which I did. I'd need to assign it a number based on the length, • 0 points if the password length is less than 6, 1 point if the password length is 6, 2 points if it is 7, 3 points if it is 8, 4 points if it is 9, 5 points if it is 10.
For that part, would an if-then statement work? I'm unsure how to format it. I'd just need one column, like D, shown, and I can figure out the rest. Thanks in advance for your help.
My apologies, I misread my assignment;it was supposed to be 2, not 1, but I adjusted the formulas accordingly. I figured out D14 and D15 thanks to your post #5, and now D16 thanks to this recent post. For D18, I also had that COUNTA formula, but the part I don't understand is how I'd assign a specific number (1, 2, 3, etc) depending on what that COUNTA number produces. For example ,if that COUNTA formula results in 8, how do I assign a cell 3. If i'ts 9, how do I assign it as 4, etc. In any case, thank you tremendously for your help so far.
The special character one worked, thank you so much!! I think I understand what you did and how you got that to work.
In regards to D18, I need to assign it "0 points if the password length is less than 6, 1 point if the password length is 6, 2 points if it is 7, 3 points if it is 8, 4 points if it is 9, 5 points if it is 10." So I found the length of each password with the COUNTA formula, just as you had, but I am confused at how I'd make it reflect those point values I quoted above. So for example, Since the length of the password in column D is 5, I'd need to assign D18 to 0.