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))+FIND("-",MID(Reference,4,1))+ISNUMBER(INT(MID(Reference,5,3))))

But, no luck.


How to do this?

Thanks a lot for your support.
sramakrishnanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
svetayeConnect With a Mentor Commented:
Hello,
There is couple of issues in you request:
1. We can't check if the text is uppercase
2. When we are using INT(text) we get an error if the text isn't a number so we can't in this case show any friendly message to the end user.

The function that should work for you exclude those issues is:

=IF(FIND("-",[ColumnName])>0,(IF(ISNUMBER(INT(RIGHT([ColumnName],3))),(IF(ISTEXT(LEFT([ColumnName],3)),TRUE,FALSE)),FALSE)),FALSE)

And you need to limit the column to the 7 chars.

I hope it will helps.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I know this question is closed already.

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(Trailer,"0000")

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@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),UPPER(LEFT(test,3))),NOT(ISNUMBER(LEFT(test,1)+0)),NOT(ISNUMBER(MID(test,2,1)+0)),NOT(ISNUMBER(MID(test,3,1)+0)),MID(test,4,1)="-",ISNUMBER(RIGHT(test,3)+1))

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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.