1

I would like to restrict the data in a cell to a numberical 00-00 format with the following requirements:

  1. The first and fourth characters need to begin with a "0" or a "1"
  2. A hyphen needs to appear as the 3rd character

Can anyone help with this Custom Data Validation formula?

Whitney
  • 11

2 Answers2

0

There may be a better way, but one (rather brute-force) way would be to just check for that particular pattern with a formula.

You'll need ISNUMBER() to check for numbers*, and you'll need MID(A1,x,1) to check the character at the xth position in cell A1.

Combine these with an AND() and you're good to go.

For the particular example in the OP you can use:

=AND(ISNUMBER(MID(C4,1,1)*1),ISNUMBER(MID(C4,2,1)*1),MID(C4,3,1)="-",ISNUMBER(MID(C4,4,1)*1),ISNUMBER(MID(C4,5,1)*1))

* Checking for numbers in text doesn't work by default. See this SE answer for how to resolve that.

LondonRob
  • 425
0

Put this on your Data Validation custom formula:

=AND(MID(A1, 3, 1)="-",
     OR(MID(A1, 1, 1)="0",
        MID(A1, 1, 1)="1"),
     OR(MID(A1, 4, 1)="0",
        MID(A1, 4, 1)="1"))

Basically what you want involves three conditions that must be met:

  1. The third character must be a hyphen.
  2. The first character must be a 0 or 1
  3. The fourth character must be a 0 or 1

If you want, you can add this to the AND to ensure that the input limited to five (5) characters only.

LEN(A1)=5

And don't forget you must format the cell as TEXT, else it will always be translated to date (although I suspect what you want is date format too)

Vylix
  • 1,748
  • Hey, sorry, we overlapped. – LondonRob May 30 '17 at 21:39
  • Thank you to you both, your responses are much appreciated. This is my first time using this site and I couldn't be happier with the results. Thanks again! @LondonRob – Whitney May 31 '17 at 12:32
  • @Whitney That's what this site is all about. If you like the answers, think about upvoting and accepting (clicking the check mark!) – LondonRob Jun 02 '17 at 15:07