Numbers in a text string are evaluated as text, so ISTEXT is no good here. As you can't use arrays in validation, you have to check each of the letters individually.
=AND(LEN(D1)=11,EXACT(D1,UPPER(D1)),ISNUMBER(--MID(D1,3,3)),ISNUMBER(--MID(D1,7,4)),ISERROR(--MID(D1,1,1)),ISERROR(--MID(D1,2,1)),ISERROR(--MID(D1,6,1)),ISERROR(--MID(D1,11,1)))
Long formula, but probably the simplest way to do it.
I didn't that ISTEXT had an issue when numbers are in a text string. This is good to know and should definitely help me fix some of my other formula's with errors. Thank you for your help jason.b75I also appreciate the simplicity of your formula.
Also thank you Glenn Kennedy your code works as well just far more advanced then jason.b75.
Thanks !
Correction Glenn Kennedy you are right since both formulas carry out the same task it cannot be more advanced
Last edited by taliacrew; 10-19-2016 at 12:25 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks