Hi - Dave here.

Happy Friday!

How can you validate a "strong" password with a formula in Excel?
 
  • At least 8 and not more than 15 characters long
  • Contains at least one uppercase (A-Z) letter
  • Contains at least one lowercase (a-z) letter
  • Contains at least one number (0-9)
  • Contains at least one punctuation character
  • Contains no whitespace

Historically, this has been a challenging problem in Excel because there have not been great tools for the job. You have to cobble together many functions to perform the tests in a formula like this:

=AND(
  LEN(B5)>=8,
  LEN(B5)<=15,
  COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B5))>=1,
  COUNT(FIND(letters,B5))>=1,
  COUNT(FIND(UPPER(letters),B5))>=1,
  COUNT(FIND({"!","@","#","$","%","^","&","*","(",")","_","+"},B5))>=1,
  ISERROR(FIND(" ",B5))
)

However, that all changed with the recent introduction of the REGEXTEST function in Excel. With regex, we can validate a strong password like this:

=REGEXTEST(B5,"^(?=.*[A-Z])(?=.*[a-z])(?=.*d)(?=.*[^ws])[^s]{8,15}$")

You can see the result in the worksheet below:

A formula to validate a strong password with regex

[Download the workbook and read the full explanation]

If you've never been exposed to regex, I know this formula probably looks like gibberish. Regex is literally an entire language for defining and detecting patterns in text, and it is notoriously complex. On the other hand, Regex has been around for decades and has great documentation. You can even ask ChatGPT or other AI chatbots for the pattern you need, and they will happily oblige :)

Since REGEXTEST is a brand new function, it is only available in the Beta channel for Excel 365. However, I've also included the traditional formula above based on COUNT, FIND, LEN, etc., that will work in older versions of Excel. Read the article for all the details, and download the workbook to try it out yourself.

Excel formulas

We maintain a list of over 500 working formulas here.

If you need more structure, we also offer video training.

Have a great weekend!

Dave

The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at one useful Excel formula. You can sign up on our home page.

 
Exceljet Logo
Exceljet
P.O. Box 4804
Salt Lake City, UT 84110

Copyright © 2024 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.