Alpha validation is tricky. While ISERROR(LEFT(C2,2)+0) will return true for "AB" it will also return true for "A1", "?A" etc.
Assuming only A-Z are allowed something like below for C2:C10 range.
Formula:
=AND(MEDIAN(CODE(LEFT(C2,1)),65,90)=CODE(LEFT(C2,1)),MEDIAN(CODE(MID(C2,2,1)),65,90)=CODE(MID(C2,2,1)),ISNUMBER(RIGHT(C2,4)+0),LEN(C2)=6)
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks