sramakrishnan
asked on
Column Validation Formula In SharePoint 2010
Dear Experts,
My requirement is to validate a column values using a validation formula for following format: <<Prefix>>-<<Suffix>>
The field should type of "Single line of text" where Prefix should be 3 uppercase letters, i.e., PRC, and middle character should be a hyphen (-) and suffix should be 3 numeric, i.e., 001. For example, PRC-0001
I tried this:
=(FIND("PRC",MID(Reference ,1,3))+FIN D("-",MID( Reference, 4,1))+ISNU MBER(INT(M ID(Referen ce,5,3))))
But, no luck.
How to do this?
Thanks a lot for your support.
My requirement is to validate a column values using a validation formula for following format: <<Prefix>>-<<Suffix>>
The field should type of "Single line of text" where Prefix should be 3 uppercase letters, i.e., PRC, and middle character should be a hyphen (-) and suffix should be 3 numeric, i.e., 001. For example, PRC-0001
I tried this:
=(FIND("PRC",MID(Reference
But, no luck.
How to do this?
Thanks a lot for your support.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@svetaye
>> 1. We can't check if the text is uppercase
That's not correct. Use Upper() with Exact(). In fact the formula you suggested does not work in most instances. I suggest this one instead (with the column name = "test"
=AND(EXACT(LEFT(test,3),UP PER(LEFT(t est,3))),N OT(ISNUMBE R(LEFT(tes t,1)+0)),N OT(ISNUMBE R(MID(test ,2,1)+0)), NOT(ISNUMB ER(MID(tes t,3,1)+0)) ,MID(test, 4,1)="-",I SNUMBER(RI GHT(test,3 )+1))
Here is a comparison table of the results between my suggestion and svetaye's suggestion:
>> 1. We can't check if the text is uppercase
That's not correct. Use Upper() with Exact(). In fact the formula you suggested does not work in most instances. I suggest this one instead (with the column name = "test"
=AND(EXACT(LEFT(test,3),UP
Here is a comparison table of the results between my suggestion and svetaye's suggestion:
entered text teylyn's formula svetaye's formula
AAA-123 TRUE TRUE
A-123 FALSE TRUE
AA-123 FALSE TRUE
AaA-123 FALSE TRUE
AAAA-12 FALSE TRUE
123-123 FALSE TRUE
123-AAA FALSE FALSE
123-aaa FALSE FALSE
Another approach would be to have two input columns and a calculated column
Leader = Input column for three letters (easy to validate)
Trailer = Input column for a number (easy to validate)
calculated column that combines the two with a hyphen using
=UPPER(Leader)&"-"&TEXT(Tr
cheers, teylyn