Use formatting rules to require and restrict data in cells

How would I add either via a macro or Data Validation formula rules in Excel 2010 for two columns with different value needs? Please find the details listed below:

Cells in Column B need look like this: "N1234" with the first character being alpha and the next four characters being numeric
Cells in Column C need to look like this: "AB1201" with the first two characters being alpha and the last four being numeric

I will format the entire column with these rules and as people enter their data the data will need to conform to these rules or the user will be unable to successfully enter the data. I am using Excel 2010.

Any help with this would be greatly appreciated.

You can do this with Data Validation.

Highlight B2:B10 (or however far you want to go) > Data > Data Validation > Allow: Custom > Formula: =AND(ISERROR(LEFT(B2)+0),ISNUMBER(RIGHT(B2,4)+0),LEN(B2)=5) > OK

Highlight C2:C10 (or however far you want to go) > Data > Data Validation > Allow: Custom > Formula: =AND(ISERROR(LEFT(C2,2)+0),ISNUMBER(RIGHT(C2,4)+0),LEN(C2)=6) > OK

Hello,

When I try to use the formula for column B and the formula for column C the following error appears for both: "A named range you specified cannot be found." Since the data validation begins on row 21 (cell B21 and cell C21) I changed B2 and C2 in your formula to B21 and C21, respectively.

Hello, this formula works for column C - I just modified it slightly to begin at cell C21. Thank you! That said, can you help with column B (which actually starts with B21)? I tried to modify what you provided for C as follows:

=AND(MEDIAN(CODE(LEFT(B21,1)),65,90)=CODE(LEFT(B21,1)),MEDIAN(CODE(MID(B21,2,1)),65,90)=CODE(MID(B21,2,1)),ISNUMBER(RIGHT(B21,2)+0),LEN(B21)=4)

However, I am sure I missed something. Column B example is: N1234 - First character is alpha value and the next four characters are numeric values.

Try...

Formula:

Please Login or Register  to view this content.

You don't need second Median()=CODE() part, as that's checking if 2nd character is A-Z.

OMG! Thank you these both work!