Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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.


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.

share|improve this answer

Your Answer


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.