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

 

[Jordytee]

Maybe:

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

Hi there this almost works! But what about if i want to continue the data validation down through the column, the whole column to be precise. I have tried replacing B2 with $E2 (E is the column where my data is) i have also tried E:E

Any ideas?

 

[Tetra201]

If you want to apply the data validation rule to the entire column E:

- select the column;
- open the Data Validation dialog;
- place the following rule in the Formula field: =$E1=TEXT(--REPLACE($E1,7,1,""),"000000-00")

 

[Jordytee]

Perfect it works! Thanks so much, I shall remember this. Take care

 

[Jordytee]

Hi Tetra, how about if i also want a specific phrase to also be allowed within the data validation rule?

 

[Tetra201]

Try

=IF($E1="Specific phrase",TRUE,$E1=TEXT(--REPLACE($E1,7,1,""),"000000-00"))

 

[Jordytee]

perfect! that worked a treat. thanks Tetra!!

 

[Scott Huish]

I think you can just use OR:

=OR($E1="Specific phrase",$E1=TEXT(--REPLACE($E1,7,1,""),"000000-00"))

Click Yes when it says evaluates to an error.

 

[Tetra201]

Scott,

No, OR will not work here because it has to evaluate both conditions. So, when we try to enter "Specific phrase" in the cell:
- the first condition evaluates to TRUE,
- the second condition evaluates to #VALUE !,
- the OR returns #VALUE !, and
- the Data Validation rejects the entry as invalid.