Data validation in excel for ssn, first name, last name, email address not working

Question

I am trying to build a regex in Excel;s data validation. However, it is not working.

Would you please explain how could I put data validation in Excel

I want last name data validation with characters, quote('), space and dot.

=ISNUMBER(MATCH("^[a-zA-Z\s,.']*$",F:F,0))

I am using above formula and, again, it is not working

For SSN, I am using following formula and it is not working

=AND(ISNUMBER(MATCH("/^\d{3}-\d{2}-\d{4}$/",A2,0)),LEFT(A2,1)>=0)

Please help me to build data validation from data tab or VBA script for the data validation.

Answer 1

The MATCH function in Excel is not used for regex matching. But you do not need it here, it looks like.

To allow only letters and ,.' you could use formula like this:

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)," ,.'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

To validate an SSN try the formula from the guide here

=AND(LEFT(F1,1)>="0",LEN(F1)<10,ISNUMBER(F1))

---

Some more pointers:

• More Data Validation examples
• Adding validation to a cell using VBA is described here and using Regex in VBA here, as noted.

Answer 2

Unfortunately, the Match Function is not able to support regex. It can only match a single, constant value.

I suggest you take a look at This very detailed post, which explains beautifully how to use Regular Expressions in Excel.

Other than that, your Regular Expression looks to be in working order - if you get this set up, it should work immediately. Good luck!