Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want a cell to only have a specified format, e.g. "01-02-03", meaning "double digit dash double digit dash double digit".

I a user tries to enter something into the cell, an error should occurr telling him if the entered value is wrong.

Is this possible without creating a macro or complex VBA-scripts?

share|improve this question
add comment

2 Answers

You can use a custom format for a 6 digit number: 00-00-00, and then use Data Validation to limit text length to 6. It will still allow non-numeric characters unfortunately.

Alternatively you use use Whole Number Data Validation between 100000 and 999999, but obviously this wouldn't allow the number to begin with a 0.

share|improve this answer
    
Sorry James looks like we we're putting in the same answer at once. I deleted mine. –  Blomkvist May 5 '11 at 9:25
    
@Blomkvist No probs, great minds think alike! –  James May 5 '11 at 9:26
add comment

Here's a validation rule that uses the following conditions:

  • There has to be 2 dashes, in the exact same positions as shown.
  • With the exception of the dashes, the value contains only numbers.

Setup:

Step1: Select your cell and create a validation rule for it.

Step2: Choose Custom and enter the formula below (replace A1 with the cell you're referring to in your question).

=AND(ISNUMBER(MID(A1,1,2)+0), ISNUMBER(MID(A1,4,2)+0), ISNUMBER(MID(A1,7,2)+0), MID(A1,3,1)="-", MID(A1,6,1)="-")

Using this validation formula, the following sample entries will show the error "The value you entered is not valid" after the user de-selects the cell or presses Enter after typing the value.

ad-24-24
56-a6-34
456-234
123456
67-7890
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.