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?
Thanks for the help guyz
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.
...and another way
Formula:
Please Login or Register to view this content.
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