Format restrictions for cell in Excel

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?

Solution 1:

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

Solution 2:

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.