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.