Excel formula for data validation using regex

Question

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?

Rachel Hettinger · Accepted Answer · 2015-06-24 22:23:50Z

3

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:

2010-2011 (valid)
abc-2011 (invalid)
2010-abc-2011 (invalid)

answered Jun 24, 2015 at 22:23

Rachel Hettinger

8,4872 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Coleman Over a year ago

The +0 trick is nice. I had a similar idea but was thrown by the fact that Left (and Mid) makes the result a string. I was looking at cumbersome expressions like NOT(ISERROR(VALUE(LEFT(... etc.

rrgirish Over a year ago

Can i apply it to an entire column instead of one cell?

tospig Over a year ago

@rrgirish yes - copy the formula down the entire column

Collectives™ on Stack Overflow

Excel formula for data validation using regex

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related