LinkBack URL
About LinkBacks
I need a custom formula to validate data so as to limit it to two capital letters and then 1 to 3 integers. Data such as such as "DB1" or "OP100" would return true and be allowed in the cell.
I need a custom formula to validate data so as to limit it to two capital letters and then 1 to 3 integers. Data such as such as "DB1" or "OP100" would return true and be allowed in the cell.
This might do it -->
See Column A attached.
=AND(NOT(ISNUMBER(VALUE(LEFT(A1,1)))),NOT(ISNUMBER(VALUE(MID(A1,2,1)))), ISNUMBER(VALUE(RIGHT(A1,LEN(A1)-2))), LEN(A1)>=3, LEN(A1)<=5,EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))))
Last edited by Xx7; 03-23-2014 at 11:58 AM.
Hi Stan,
Using what Xx7 did above and making it a little simpler, Try:
=AND(NOT(ISNUMBER(VALUE(LEFT(A1,1)))),NOT(ISNUMBER(VALUE(MID(A1,2,1)))), ISNUMBER(VALUE(MID(a1,3,10))))
Last edited by MarvinP; 03-23-2014 at 01:53 PM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Marvin, don't you need capitalization criteria and total string length criteria? Or can this be entered as separate validation criteria?
Hi Xx7,
I simply used your first half of the data validation and took from character 3 to 10 and tried to see if it was all numbers. It made my formula a little shorter and allowed for 1 to 7 numbers after the first 2 letters. No UCASE involved with my formula.
Thanks for the help guyz
...and another way
Formula:
AND(AND(CODE(A1)>=65,CODE(A1)<=90),AND(CODE(MID(A1,2,1))>=65,CODE(MID(A1,2,1))<=90),VALUE(RIGHT(A1,3))<=999)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Another option:
=AND(ISERROR(--MID(A1,1,2)),--MID(A1,3,3)>0)
Quang PT
Hi Quang,
I was really hoping your answer would work. It needs to show false if the first two characters are not letters. I tries W31234 and it was allowed by your formula above. I think you are close to a better answer. Have another try?
Thhanks Marvin, it was my failure. Maybe this works:Originally Posted by MarvinP
=AND(ISERROR(--MID(A1,1,1)),ISERROR(--MID(A1,2,1)),--MID(A1,3,3)>=0)
Closer.
I tried AB123A and it was ok. I don't think it should have been accepted. I reread the problem and he wants exactly two letter and then 1,2 or 3 digits.
My formula didn't account for this second part either.
Good observation, Marvin. A lenght condition should be considered:
=AND(ISERROR(--MID(A1,1,1)),ISERROR(--MID(A1,2,1)),--MID(A1,3,3)>=0,LEN(A1)<6)
Thank for the rep, Marvin.
A B ###
Test if first 2 are not numbers, next 3 are numbers with:
ISERROR(--A),ISERROR(--B),--#,--#,--# = TRUE,TRUE,TRUE,TRUE,TRUE
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks