Excel formula for data validation using regex

2
votes

I want to do data validation using excel on a column of the type:

year followed by "-" followed by another year.

example 2010-2011 Can this be done using excel formulas? Can we use regex for this?

1
excelexcel-formula

1 Answers

3
votes

Try this formula (assumes A1 contains value to validate):

=AND(LEN(A1)=9, MID(A1,5,1)="-", ISNUMBER(LEFT(A1,4)+0),ISNUMBER(RIGHT(A1,4)+0))

This will correctly identify the following examples:

  1. 2010-2011 (valid)
  2. abc-2011 (invalid)
  3. 2010-abc-2011 (invalid)