Hi Greg, I apologize, i feel like rainman now.
the input would ocure in the same cell (your A1 - my E15), either as
######
or
################ (16x #) actually, since excel cannot work with 16 digit
numbers, the cell IS formatted to text and the formatting would actually be
@'s instead of #'s

Once the data was input as shown above the output would change to
@@xx xxxx xxxx @@@@ or
@@@@ @@@@ @@@@ @@@@

The formula i have does check and format as wanted, but i dont know how to
apply this to datavalidation.
..=IF(LEN(E15)=6,LEFT(E15,2)&"xx xxxx xxxx "&RIGHT(E15,4),IF(LEN(E15)=16,LEFT
(E15,4)&" "&MID(E15,5,4)&" "&MID(E15,9,4)&" "&RIGHT(E15,4),"NOT"))
Thanks and greeting from Nevada.


Greg Wilson wrote:
Assuming by six you mean "###/###":

'=AND(LEN(A1) = 7,
ISNUMBER(VALUE(LEFT(A1,3))),MID(A1,4,1)="/",ISNUMBER(VALUE(RIGHT(A1,3))))

Assuming by 16 you mean "########/########":

'=AND(LEN(A1) = 17,
ISNUMBER(VALUE(LEFT(A1,8))),MID(A1,9,1)="/",ISNUMBER(VALUE(RIGHT(A1,8))))


--
Message posted via http://www.officekb.com