Data Validation

[CHuguley]

I need to have a cell entry entered as: 400-xxx-xxx. I've entered all the necessary data (input message and error message). The problem appears to be in the settings. I custom formatted the cell but when I enter it correctly or incorrectly, I get the error message. Any help would be appreciated.

 

[FinancialAnalystKid]

How did you customize the cell? What settings did you use in your validation? Is it text length? If so, change it to 12 not 10 to accomodate for the dashes that your user enters.

 

[R_Stephens]

Hi there

I'm assuming that the xxx implies a three digit number?

If not let me know and we will change the following

For data validation in cell A1, use the following

Choose Custom Data Validation
in the formula box put:

=IF(AND(LEN(A1)=11,VALUE(MID(A1,1,3))=400,ISNUMBER(VALUE(MID(A1,5,3))),ISNUMBER(VALUE(MID(A1,9,3))),MID(A1,4,1)="-",MID(A1,8,1)="-"),TRUE,FALSE)


This will ensure that you get the format 400-xxx-xxx
Anything else will be rejected.

Hope this helps

Richard