Formatting requirements/restrictions for data entered into cells (formatting masks)

[HedyES]

Hello,

I would like to restrict/require certain data in cells entered into to different columns as follows:

In column B the format needs to look like the following example: N1234 with the first character being an alpha value and the next four need to be numeric values

In column C the format needs to look lie the following example: AB1200 with the first two characters being alpha values and the next four need to be numeric values

I would prefer to use Data Validation instead of macros, but whatever works will be fine. I am working in Excel 2010

Thank you

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...e-and-restrict-data-in-cells.html#post4966420

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[HedyES]

Cross posted https://www.excelforum.com/excel-fo...e-and-restrict-data-in-cells.html#post4966420

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Is there a way to delete this post?

 

[Fluff]

No need to delete this thread, all we ask is that you let people know that you have asked the same question elsewhere.
Also if you get a solution, please update this thread with the solution, so that we know that you are no longer in need of help.

 

[HedyES]

This is the solution for my original post:

Column B (starting with cell B21):
=AND(MEDIAN(CODE(LEFT(B21,1)),65,90=CODE(LEFT(B21,1))ISNUMBER(RGHT(B21,4)+0),LEN(B21)=5)

Column C (starting with cell C21):
=AND(MEDIAN(CODE(LEFT(C21,1)),65,90)=CODE(LEFT(C21,1)),MEDIAN(CODE(MID(C21,2,1)),65,90)=CODE(MID(C21,2,1)),ISNUMBER(RIGHT(C21,4)+0),LEN(C21=6)

 

[HedyES]

I posted the solution in case anyone needs this same information. How do I mark this post as solved?