?
Solved

Column Validation Formula In SharePoint 2010

Posted on 2012-03-25
3
Medium Priority
?
2,979 Views
Last Modified: 2012-03-25
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.
0
Comment
Question by:sramakrishnan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
svetaye earned 2000 total points
ID: 37762460
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
 
LVL 50
ID: 37763840
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
 
LVL 50
ID: 37763879
@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

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange users below are the steps you can follow to upgrade your Lync server to latest CU's or cumulative updates. Note: Perform it during non-production hours.   Step 1: Backup your lync and SQL server database. Follow below article: h…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question