Need Data Validation formula for 16 digit numbering with '-'

[kal-el.Kanata]

I have been trying to figure out the data validation formula to require users to input this specific varchar numbering 1234-123-1234567

Can someone please provide me with some help?

MS Excel 2012 and later

[6StringJazzer]

This works for me, where E7 is the cell containing the data validation:

Please Login or Register  to view this content.

[kal-el.Kanata]

Thanks however, one issue when I try to apply it to multiple cells in a col like this

=AND(LEN(F27:F37)=16,ISNUMBER(VALUE(MID(F27:F37,1,4))),MID(F27:F37,5,1)="-",ISNUMBER(VALUE(MID(F27:F37,6,3))),MID(F27:F37,9,1)="-",ISNUMBER(VALUE(MID(F27:F37,10,7))))

It doesn't seem to work.

It throws the Value no valid error.

Thoughts?

[6StringJazzer]

You can't use ranges like that. The formula has to refer to the cell containing the data. Set up your data validation in F27, then copy the cell to F28:F37 and the data validation formula will be taken along with it. (I don't remember if there is a "paste special -> validation" option in 2003. You have a pretty old version of Excel.)