Data validation on number of numerical characters in a string-CodePudding

Trying to add formula to data validation that checks whether a string is either 6 numerical digits, or 6 numerical digits with hyphens or spaces. E.g.

123456 Or 12-34-56 Or 12 34 56

The string could also contain leading zeros.

The user should not be able to input a string that differs from the above formats, so

123-456 Or 1234567 ....for example

Would prefer not to use VBA, but am struggling to make this into a data validation formula.

Any ideas would be appreciated

CodePudding user response：

Try this:

=AND(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-","")*1<=999999,SUBSTITUTE(SUBSTITUTE(A1," ",""),"-","")*1>=1,LEN(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""))=6)

This formula removes hyphens and spaces, then checks if the number is 6 digits and that it falls between 1 and 999999. If there are any letters/symbols, the *1 part will cause an error.

Edit: @JvdV id right. I misread your requirements. Here is my second attempt:

=AND(OR(ISNUMBER(MATCH("??-??-??",A1,0)),ISNUMBER(MATCH("?? ?? ??",A1,0)),AND(IFERROR(A1*1,0)<=999999,IFERROR(A1*1,0)>=1)),ISNUMBER(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","")*1))

Here is what it allows and doesn't allow:

CodePudding user response：

It's a stretch for now but what about:

=AND(TRIM(A1)=RIGHT(A1,8),MID(RIGHT(TEXT(A1,"00-00-00"),8),3,1)=MID(RIGHT(TEXT(A1,"00-00-00"),8),6,1),LEN(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""))=6,ISNUMBER(--SUBSTITUTE(SUBSTITUTE(A1," ",""),"-","")))