Solved

Column Validation Formula In SharePoint 2010

Posted on 2012-03-25
3
2,840 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

This is a little timesaver I have been using for setting up Microsoft Small Business Server (SBS) in the simplest possible way. It may not be appropriate for every customer. However, when you get a situation where the person who owns the server is i…
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…
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…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

919 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

18 Experts available now in Live!

Get 1:1 Help Now