Solved

Column Validation Formula In SharePoint 2010

Posted on 2012-03-25
3
2,819 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
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
svetaye earned 500 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

Expert Comment

by:teylyn
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

Expert Comment

by:teylyn
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now