Solved

Column Validation Formula In SharePoint 2010

Posted on 2012-03-25
3
2,893 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:Ingeborg Hawighorst
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:Ingeborg Hawighorst
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Back in July, I blogged about how Microsoft's new server pricing model, combined with the end of the Small Business Server package, would result in significant cost increases for many small businesses (see SBS End of Life: Microsoft Punishes Small B…
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
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). …

821 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