Explanation
In this example, the goal is to check for “strong” passwords.
What makes a password strong depends on the rules it must follow.
Each rule must be checked with a combination of functions, resulting in a large and unwieldy formula.
REGEXTEST function
Excel’s REGEXTEST function tests for the existence of text defined by a givenpattern.
The result from REGEXTEST is TRUE or FALSE.
Lookaheads do not move the cursor forward in the string or consume characters.
They just verify if the required pattern is present.
Because lookaheads do not consume characters, they can be combined to enforce classic AND-style logic.
This pattern allows any character except whitespace.
Note: When you use multiple lookaheads, they check for conditions without moving the cursor or consuming characters.
Yes, but it’s more work.
If all tests return TRUE, the AND function will return TRUE.
Otherwise, AND will return false.
In all, we run seven tests.
When FIND locates a number, it returns its numeric position.
When a number is not found, FIND returns a #VALUE error.
The COUNT function then returns a count of numeric positions.
If the count is greater than or equal to 1, the expression returns TRUE.
Otherwise, it returns FALSE.
For a more detailed explanation,see this page.
Since FIND is case-sensitive, it will only match lowercase letters.
As before, when FIND locates a match, it returns its numeric position.
When a match is not found, FIND returns a #VALUE error.
The COUNT function returns a count, which we check with >=1.
The test for an uppercase letter works the same way.
If FIND does locate a space, it returns a numeric position and ISERROR returns FALSE.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
When the text is not found, FIND returns a #VALUE error.
LEN will also count characters in numbers, but number formatting is not included.
UPPER Function
The Excel UPPER function converts a text string to all uppercase letters.
Numbers, punctuation, and spaces are not affected.
