Data validation and numbers + hyphens

[Jordytee]

Hi there all, i want to set a data validation rule so that only the following number sequence can be entered to a cell: 000000-00
the hyphen is a must, if i didnt need the hyphen I would be fine setting this up. Any ideas?

I use the data validation from the ribbon, and need a custom formula

 

[pgc01]

Hi
Welocome to the board

For ex., for B2:

=AND(LEN(B2)=9,ISNUMBER(-MID(B2,ROW(INDIRECT("1:6")),1)),MID(B2,7,1)="-",ISNUMBER(-MID(B2,8,1)),ISNUMBER(-MID(B2,9,1)))

 

[Scott Huish]

I think this will work:
=AND(LEN(REPLACE(B2,7,1,""))=8,MID(B2,7,1)="-",ISNUMBER(SUBSTITUTE(B2,"-","")+0))

 

[Tetra201]

Maybe:

=B2=TEXT(--REPLACE(B2,7,1,""),"000000-00")

 

[pgc01]

Hi Scott

I think that will work for most of the values that the user might try, but to be sure that the pattern is respected I believe you have to check each character separately.

Your formula accepts:

1,2345-67
12.345-67
12345-67 (space before)
123456-7 (space after)
2019-1--1
10:21:-23
123456-e2
12345e-+3
+1234-56 (space before)

 

[pgc01]

Maybe:

=B2=TEXT(--REPLACE(B2,7,1,""),"000000-00")

Can't test it now (because my eyes are closing) but seems a great solution. Much simpler than mine.

 

[mikerickson]

How about

=AND(VALUE(1&LEFT(F3,6))>=100000, MID(F3,7,1)="-", VALUE(1&RIGHT(F3,2))>=100)

 

[pgc01]

Hi Mike

In the case of your formula you can trick Value() with the exponential

Try:

123456-e7
1.23e8-11


Like in Scott's case, maybe not really important, but always good to know.

 

[mikerickson]

How about =VALUE("1" & LEFT(A1,6) & ".0")?

 

[pgc01]

Yes, it seems it to also work ok.