A problem was recently posted on one of our SharePoint Solutions Help Community Forums. The situation was that an individual wanted to “roll up” multiple discussion boards from multiple sub-sites; essentially pulling the content from different discussion board lists.

One solution is to upgrade to SharePoint 2013 and take advantage of all the new social features such as Communities. Another option is to buy a third-party web part such as this one. Neither of these may be possible in your organization.

In this post, I will present a solution that involves modifying the styles available for presentation within the Content Query Web Part.

The content query web part can roll-up information from multiple sites, based on Content Type. Unfortunately, discussion boards in 2010 are not like most lists. Only the first post in each thread has a “Title.” That first post is the Discussion Content Type, which is actually a folder with metadata. All the replies are Message Content Types within that folder. This means that if you don’t limit the content query to only folder-type content types then you receive many, many items that display “(blank)” for a placeholder for the missing Title field. Another problem is that each of the links will take you to a page with just that single message instead of showing the entire thread. In this age of Facebook and ongoing social dialogue, this setup doesn’t work for most people.

Begin by editing a page, inserting a web part, selecting “content rollup”, and choosing content query.

Edit the web part and set it to query all Discussion Board “Folder Content Types” group from the parent site of the Discussion Boards you want to roll up. In the example below, my discussion boards were in separate subsites of the Production site.

Initially, the Content Query web part looks like this.

Clicking on any of these links take the user to a single page displaying only that message:

Clicking through obviously doesn’t show the whole thread and clicking “close” takes the user back to the page with the query. This is not what most users are expecting.

This is an example of a style. A style is simply a markup template that is applied to the information you give it. The good news is that styles can be added to or modified. Each style is pulled from a specific file on your SharePoint installation, ItemStyles.xsl. So one way to make this look better and act better is to modify those styles. A familiarity with the basics of computer languages and/or markup languages comes in very handy, as I found out. The location of these styles is found in a folder on your top level site in a site collection. If you open up your site with SharePoint Designer and choose the topic of “All Files” in the left-side menu then you will be able to browse to the folder “Style Library” –> “XSL Style Sheets”. In this folder there is a file called “ItemStyle.xsl.” You can edit this item from within Designer. I added the following into this file right before the last /xsl:template tag. Because this is a markup language it gets swallowed up by web browsers in its correct syntax so I couldn’t copy and paste the text in this blog. Here’s an image of the correct lines.

This inserts a new style into the ItemStyle.xsl using the XSL standards. I save the document and return to SharePoint. Now I can choose the style I just created when editing the web part.

So now when I select the “CustomRollup” style I get this:

Not too impressive until we click on one and receive this:

Now we get links that take us to the full discussion with the default view for that discussion, whether flat or threaded. Rather than a “close” button, the browser’s back button will return us to the appropriate page. You can add to the code in order to make it look better. I got a lot of help from W3schools where I learned that XSL was simpler than I thought and from Stackoverflow.com for specific help on getting a hyperlink displayed properly. This instruction set makes much better and more advanced use of XSL to provide an experience in styles.

There are limitations and problems with this solution (just like any other) in that if Microsoft ever chooses to update the file, your custom changes will probably be overwritten without notice. I will continue to play with this and see if I can make it better looking, but I hope this helps you get started on aggregating your discussion boards for a better user experience in SharePoint 2010.

The ostensible purpose of the Calendar Overlay feature in SharePoint 2010 is to aggregate multiple calendars into one master, or roll-up calendar. The appointments in each calendar can display in an assigned color, allowing for a quick visual impression of which appointments belong on which sub-calendar.

In my previous blog post, I described how the SharePoint 2010 Calendar Overlay feature could be used to color-code individual appointments in a single calendar. The trick is to create views of types of appointments (based on perhaps a custom metadata column you might add to the Calendar). The Calendar Overlay can then be used to assign a color to each view. Then, each appointment in that view will have the assigned color.

This post shows another feature of Calendar Overlay: Showing items that are not on a calendar. In our example, the IT department has the default Team Site Calendar that they use for general items. In addition, they would like to include on this calendar the anticipated end date for their major projects, and the review date for the various contracts they have with vendors. This information is already captured in a Project List and a Contracts and Agreements Library. Both the Library and the List have a column for a date. Using these I can (a) create a view for each one that is a calendar, then (b) use Calendar Overlay to roll those dates up into a master Team calendar.

The first step is to create Calendar views of the non-calendar lists / libraries. Below is the Contracts and Agreements Library in the typical list format. Each document has a Review Date; that will be the basis for the Calendar view.

1. Go the Library ribbon and click the Create View button.
2. In the Create View page, click the Calendar View link.
3. Type in a Name for the view, such as Review Date Calendar.
4. We have only one relevant date column in this Library, so we’ll choose Renewal Date in both the Begin and End fields in the Time Interval section. Use the document Name as the item heading in the Month, Week, and Day views.
   
5. Add any other needed settings and click OK.

This gives us a view of the Library in a Calendar grid, based on the Review Date.

Here is the list of Projects that the IT department is currently working on.

Following the same steps outlined above with the Library, create a Calendar view for the list, using the Completion Date as both the Begin and End date for the Time Interval. (We want to show only the Completion Date on the Calendar, not the entire span of the project from start to finish.)

The resulting Calendar view looks like this:

Now that we have Calendar views of the Library and the Project List, we can use Calendar Overlay to roll them up into the department’s main SharePoint Calendar, shown here:

Following the steps outlined in the previous post on Calendar Overlay, add the Library and Project List to the Calendar. Briefly:

1. In the target Calendar, click the Calendars
   Overlay button in the Calendar ribbon.
   
2. Click the New Calendar link and add the relevant information for the Contracts and Agreements Library.
   
3. Repeat to add the Projects List.
   

The main IT Calendar now includes not only the events placed directly on that calendar, but the dates from the Library and Project List as well.

One of the most common requests I received as a SharePoint administrator was to color-code Events. Users are accustomed to assigning colors to appointments in Outlook and other calendaring applications, so it’s natural to want that type visual organization in SharePoint as well. Unfortunately, in SharePoint 2007 and earlier, there was no built-in solution. It was such a common need that many administrators, developers, and power-users devised solutions using various types of code. These required the know-how to develop (or the know-how to research and try to adapt to your situation), and most end-users would not be comfortable with trying such a solution.

The good news is that SharePoint 2010 includes functionality that can be leveraged to achieve this effect, with no code needed. It may not be a completely obvious solution when working with a single Calendar, though. The secret lies in the use of the Calendar Overlay. Typically this feature is used to roll up multiple Calendars (or lists with Calendar Views) into one master Calendar. Links to the sub-Calendars are included in the Calendar Overlay definition, a color is assigned to each sub-Calendar, and the user can easily understand which Events belong to which sub-Calendar. We teach this feature in detail in our two introductory SharePoint 2010 classes: Introduction to SharePoint 2010 – Using SharePoint Foundation 2010 and Introduction to SharePoint 2010 – Using SharePoint Server 2010.

What if I have Events on a single Calendar, but want to assign different colors to different types of Events, however? I might want meetings to be one color, deadlines another color, informational Events a third, HR Events a fourth, and so on. Colors cannot be applied ad hoc, but if I have the Events flagged in such a way that I can create a filtered View of those Events, I can then assign a color to those types of Events in a master View.

Add Metadata

The first step is to set up your Calendar with the appropriate metadata Column or Columns so that each event may be categorized. The Calendar has a built-in Category Column; you may wish to use this, or use it with modifications. You might also set up your own Column. For example, you might wish to categorize items on a Calendar based on the department or workgroup it belongs to. For the example below, though, I will modify the default Category Column.

1. On the page for the Calendar you wish to work with, click the Calendar tab, then click List Settings.
   
   
2. In List Settings, click the Category Column.
   
3. Choose Yes for Require that this column contains information. Update the choices under Additional Column Settings, and change the setting for Allow Fill-in Choices to No
   
   
4. Click OK.

Once the Column has the desired options, update the existing Events to include the correct Category. (If you have many Events to edit, you may want to add the Category Column to the All Events View, then use Datasheet View to quickly edit each item.)

Any new Events will be categorized as they are created.

Create Views

1. In the Calendar tab, click Create View.
   
2. You want to copy the default Calendar View and make a small change to it to add a filter. On the Create View page, click Calendar under Start with an existing view.
   
3. On the Create Calendar View page, type a name in the View Name field (i.e., Meeting Events).
   
4. In the Filter section, add settings to show only Events where the Category is Meeting. (Be careful to type the criteria exactly as it appears in the Column, including the correct case.)
   
   
5. Click OK.

Create Calendars Overlay

1. On the Calendar tab, click the Calendars Overlay button.
   
2. On the Calendar Overlay
   Settings page, add the Calendars (or in this case the Views of this Calendar) you wish to see by clicking the New Calendar link.
   
3. On the New Calendar page, type in a name for the Calendar (in this case, a View of the same calendar).
   
4. Choose a color for this Calendar View. (The colors available will depend on the theme or color scheme for the site as a whole.)
   
5. Next, SharePoint needs to know the URL of the site (or subsite) for the Calendar. By default, it fills in the URL of the subsite you are working with. In this case, that is correct. If you were trying to bring in a Calendar from another subsite, you would type or paste in that URL. With the correct URL in place, click the Resolve button.
   
6. When the URL resolves, the List field just below will be populated. From this drop-down, choose the Calendar you are working with. There may be only one selection if there is only one calendar on this subsite.
   
7. Next, choose the specific View you wish to use in the List View field.
   
8. Click OK.
9. You will be returned to the Calendar Overlay Settings page, where you’ll see the calendar addition.
10. Repeat these steps to add the Calendar Views for HR, Social, and Training, choosing a different color for each.
    
11. Return to the Calendar. Now each type of Event has its own color. In addition, you can use the buttons on the left to show only one type of Event at a time. There’s a lingering problem, though … each Event is listed twice, since the Calendars Overlay layers the other Calendars/Views on top of the Events that already show in the normal Calendar View. Therefore, we’ll need to tweak the normal Calendar View to hide the duplicates.
    
12. In our Calendar, every item will have a Category; that Column will not be blank in any instance. Therefore, it will be easy to hide the Events in the default View with a Filter that is looking only for uncategorized items. To do this, go to the View selector in the navigation breadcrumb and choose Modify this View.
    
13. In the Filter section, make selections to build a filter where the Category – is equal to – [blank].
    
14. Click OK.
15. You will be returned to the Calendar with the Calendar View and the Calendars Overlay active. Since there are no Events in the normal Calendar View that have a blank Category, all items are hidden, but the overlaid views for each Category are visible with the color coding.
    

The Content Query Web Part is a very handy tool in SharePoint Standard and SharePoint Enterprise. The web part has a number of uses. For one, it can allow you to create an aggregated list based on multiple lists or libraries, either from within a single site or from across a site collection, as we show in our course Introduction to SharePoint Server 2010. For another, it can also be used to display items from a single list or library on a different site within a site collection. For example, you might have a master Contact list on the top-level site in a site collection, but need to display a filtered version of that list on a sub site through the Content Query Web Part.

You can add the Content Query Web Part (often called CQWP for short) to a SharePoint page by going into Edit mode (Site Actions > Edit Page), then click the Insert tab and choose Web Part. Click the Content Rollup folder and select Content Query Web Part.

This blog post isn’t intended as a description of how to use the CQWP, but to point out a SharePoint site collection administration detail that sometimes causes a problem. That problem is that frequently a user will go to insert the CQWP and find that it’s missing. They are sure they are in the Standard or Enterprise edition of SharePoint (as opposed to Foundation), but the web part simply isn’t there. Why? And how do you make it available?

The answer lies in Site Collection Features. The Features are a series of options in SharePoint that can be turned on or off. Some are on by default when a site collection is created; others have to be activated before they can be used. Be aware that sometimes the name and description of a feature doesn’t tell you everything that the feature controls. In this case, the CQWP is part of a bundle of features called SharePoint Server Publishing Infrastructure. Generally we think of Publishing in SharePoint as a way to enable content management, including combining content with specific page layouts. It’s a feature commonly used by public web sites meant to provide large amounts of information (or frequently changing information) in formats similar to a newspaper or catalog. Activation of the Publishing Infrastructure is necessary, however, to make the CQWP (and certain other web parts) available for use.

A user with Full Control or Site Collection Administrator permissions can take the following steps to activate the Publishing Infrastructure:

1. Go to Site Actions > Site Settings
2. In the Site Collection Administration section, click Site collection features
   
   
3. On the Site Collection Features page, click the Activate button next to SharePoint Server Publishing Infrastructure.

Once this feature has been activated, the Content Query Web Part should then be available throughout the Site Collection.

NOTE: In addition to Site Collection Features that may be activated or deactivated, there are Features that may be turned on or off on each site; this includes the top-level site. If you want to use the full set of Publishing tools, you must also activate the SharePoint Server Publishing site feature on each site/subsite where you wish to use them.

1. Go to Site Actions > Site Settings
2. In the Site Actions section, click Manage site features
   
3. On the Site Features page, click Activate for SharePoint Server Publishing.

Keep this tip in mind whenever you can’t find a tool or feature in SharePoint – it might just be part of a Site Collection Feature or Site Feature that needs to be activated. In addition, there are some features that are enabled at the server level, so you may need to contact your server administrator if you can’t find it in the Site Collection or Site lists.

In our Introduction to SharePoint Foundation / Server 2010 course, we briefly mention that SharePoint limits the total URL length of any location in a site: Depending on some variables, it can be up to 256 to 260 characters. (See http://technet.microsoft.com/en-us/library/ff919564.aspx for a technical explanation.) In addition, you’ll run across a 255-character limit for URLs in lots of other places. For example, if you want to paste a link into an email, your email program may not accept URLs longer than 255 characters. In some SharePoint sites it can be surprisingly easy to hit that character limit. Some of the things that can eat up URL space are:

• Long names for lists or libraries
• Use of folders and subfolders
• Long names for documents or list items
• Multiple levels of subsites

In this post I’ll discuss how to help keep that length under control by smart management of lists and libraries.

Creating Lists and Libraries

First, let’s look at what happens when you create a List or Library. (In the example below, we’ll be working with a Library, but the same principles apply when working with a List.) When you open the Create dialog box, the first field that you fill in is Name. Whatever you type in as the name becomes part of the URL for that List or Library. Browsers cannot understand certain special characters, though, so they can’t be used in URLs. Spaces are one such special character, so SharePoint replaces every space with %20 to encode it in a way that browsers will understand. For example, let’s say I need to create a Library for policies and procedures on the Human Resources subsite of an intranet Site Collection. I might be a bit verbose on top of that, so I name my new Library Policy and Procedure Documentation. SharePoint uses the Name as both the display name (for the Library page, the Quick Launch Bar, etc.) and the library URL. In the URL, the %20 code is used instead of the spaces. So, I end up with something like http://intranet/sites/humanresources/Policy%20and%20Procedure%20Documentation. In addition to being long, the URL is also not very user-friendly – the space encoding makes it difficult to “read.”

So, how do we create Lists and Libraries in such a way that the URL will be shorter and more user-friendly, while also having as descriptive a display name as possible? The trick lies in creating the List or Library with a brief name first, then going back and updating the display name. For example, in my Human Resources subsite I’ll go to Site Actions > New Document Library to open the Create dialog box. Instead of naming it Policy and Procedure Documentation, I’ll name it policies. If I really wanted to include “procedures” in the URL, I might do something like policies_procedures, substituting an underscore for a space.

(Note that I also turned on Versioning for this library; more about that later.)

After I click Create in the dialog box, I’ll have a library that looks like the example below. The URL includes policies, and that’s also the display name in four locations: The descriptive text below the column headings, the breadcrumb navigation at the top of the page, the link in the Quick Launch Bar, and the browser tab (page title):

Since I want that display name to be more formal and descriptive, now I’ll go into the Library Settings to modify it. I’ll click on the Library

tab under Library Tools, then click on the Library Settings button.

In Library Settings I’ll click Title, description, and navigation under the General Settings heading.

On the next page, I’ll see that the current Display Name is policies. I’ll change that to my preferred Display Name, Policies and Procedures Documentation, and click Save.

before:

after:

Returning to the Library, I see that my display names have updated, but the short, user-friendly URL hasn’t changed.

Folders

When creating folders, not only do you have to be aware that a folder just creates another level in the URL path, but you are also limited to no more than 128 characters when creating a folder name. As we discuss in the Introduction to SharePoint 2010 Foundation / Server class, the best practice in SharePoint is to not use folders at all, but to use metadata (columns) to categorize items. By using this flat structure with metadata, you can categorize items in a number of ways instead of just one way as you would with a folder. In addition, you can then create views to display the items according to a wide range of sorting, filtering, and grouping criteria. These metadata terms are also available as Search criteria. There are times when a folder is truly necessary, such as when a group of items has a unique permissions requirement. Otherwise, however, use metadata.

For example, in our Policies and Procedures Library, we might have had one folder for Policies and one for Procedures. Instead, I’ve set up the library with multiple metadata Columns so that each document may be flagged in a number of ways by going to Library

tab > Library Settings button > Create Column link.

For detailed information about using metadata and views, see our free online video SharePoint 101 – What Makes SharePoint Work.

Item Names

It is also helpful to refrain from having document names that are very long. In the example above, there is a document called Employee Dress Code Policy – Revision 06-2012. SharePoint converts spaces in names to %20 for the purposes of a URL, since browsers can’t understand some special characters like spaces. So, the URL for that document would be http://intranet/sites/humanresources/policies/Employee%20Dress%20Code%20Policy%20–%20Revised%2006-2012. Smart document naming and management habits can keep that name shorter and more intelligible. First, the word “policy” could be dropped, since the Category flag already indicates that it’s a Policy. It could also be argued that the word “Employee” could be removed, since it is implied that the policy applies to the company employees. Next, the revision information can be removed. For one thing, each item has a Modified date/time stamp, so the last revision date is easy to view. In addition, by turning on Versioning as we did when we created the library, we can avoid the phenomenon so common to shared drives: Multiple copies of a document are stored, each with a different revision date. All of the revisions are tracked in this single document and can be viewed, or even restored, at any time.

Bonus Tip: Subsites

When you create a subsite, you have an advantage that you don’t have when creating a List or Library: Separate fields for specifying the name and the URL. You might have a site name that’s spelled out (Human Resources) but you are not stuck with that as the URL. In the example above, it makes sense to use the common acronym for Human Resources, HR. So instead of http://intranet/humanresources, I can use http://intranet/hr. Just be sure that if you use acronyms or other shortened names for site URL paths, that it’s something that will be reasonably intelligible to the users.

By combining best practices for naming, document management, and List / Library management, you can keep your URLs to a reasonable size as well as improve the functionality and ease of use for the site as a whole.

Hi, and welcome to my first blog post for SharePoint Solutions. I’m looking forward to meeting more of our students and clients in the coming weeks, but I thought I’d jump in and introduce myself through the blog. Today I want to talk about a basic topic that is nonetheless a little puzzling at first glance – the difference between the Blank Site template and the Team Site template in SharePoint 2010.

You might be saying to yourself, “Well, that’s obvious; the Blank Site is empty, while the Team Site has some features already built out of the box.” That’s true, but the difference goes a little deeper; it can be a significant factor in which template you choose when creating a site collection or subsite.

As you probably know, SharePoint comes with a number of site templates available; the number depends on which edition of SharePoint you are running, but all editions have both the Blank template and the Team template. These templates also existed in previous versions of SharePoint. In the 2007 version and earlier, the Blank and Team templates were identical except for the fact that the Team template had a library and several lists already created and in place. In SharePoint 2010, however, that difference goes deeper.

One of the things we discuss in the class Introduction to SharePoint 2010 – Using SharePoint Foundation 2010 is the existence of three types of web pages in SharePoint: System pages, web part pages, and wiki pages. (In the course Introduction to SharePoint 2010 – Using SharePoint Server 2010 we also use a fourth type, publishing pages.)
In previous versions of SharePoint, the home page for all site templates was a web part page. You edited the home page by adding (or removing) web parts, including perhaps a Content Editor Web Part if you wanted a block of text, or an Image Web Part if you wanted a graphic. In SharePoint 2010, however, the home page of most web sites is a wiki page, and it is stored in a new type of library called Site Pages. These sites also have a library called Site Assets, where files like images for the wiki pages are stored.

The Team Site template is one of the templates that uses the wiki page as a home page. Because of this, you can easily edit the page right in the browser. When you go into Edit mode, you can delete the default text and add new text, just as you would in a word processor. You can add images, tables, and other elements. You also have a number of options for formatting text and changing the page layout, all with tools that appear in the contextual ribbon. This is a WYSIWYG interface that we are all used to in the modern computing world – what you see is what you get, and you can touch and change what you see directly.

In the Blank Site template, though, the home page functions like home pages in previous versions of SharePoint. You must use Web Parts for adding any content, including text and images. On a Blank site, the Site Pages and Site Assets libraries don’t exist. If you want a wiki page for the home page (or any other site content), you have to create the two libraries and the page from scratch, then create a new wiki page and flag it as the new default for the site if desired. Even if you don’t need to change the type of page for the home page, you still have to create the Site Pages and Site Assets libraries (or equivalents) if you want to create any other wiki pages for the site, because pages need to be stored in a library.

So, when should you choose the Blank Site template, and when should you choose the Team Site? My general rule of thumb is that I usually use the Blank Site only if I know that none of the usual Team lists are going to be needed (i.e., Calendar, Announcements) and that having a web part page instead of a wiki page will be acceptable. In these cases the content of the site is often planned to be highly customized by an experienced site owner or developer. Otherwise, I’ll just start with a Team site and just delete or hide those lists and libraries that won’t be used. That’s easier and faster than starting with the Blank Site and building out the wiki pages and associated library if they are desired.

In my role as a SharePoint administrator and consultant, I usually created sites for others to manage. Many of these site owners were new to SharePoint, or at least new to managing a SharePoint site. It was helpful for them to have some elements already in place so they could start to see the range of tools that SharePoint has. Being able to edit the home page without learning the intricacies of Content Editor Web Parts and Image Web Parts was helpful, too. This greatly reduced the learning curve for them and helped them get up and running more quickly.

Two years ago I wrote a blog post about How to Create a Detailed Custom Task Notification with a SharePoint Designer Workflow. That blog post provided step-by-step instructions and screenshots for doing this in SharePoint 2007. I received a lot of questions asking for more information explaining the steps. Back in December I finally decided to record a video of showing how to do this same thing in SharePoint 2010. However, I got tied up with other work and just now realized that I had never posted that video here on the blog. Oh well, better late than never, I guess.

I think you’ll find that I explain things in much more detail in this video and you get to see all the steps that are involved as well. As always, if you have questions, let me know in the comments and I’ll do my best to answer them as time allows.

Click here to watch the video on YouTube.

Wow! I never expected to write so many posts about Column Validation. Yet, here I am with the 5th post in what has become a series about Column Validation. Just to recap, here are the links to my previous posts on Column Validation:

•  How to Validate a Due Date in a SharePoint 2010 List
•  How to Validate Strict Text Formats in SharePoint 2010
•  How to Validate an Email Address Using Column Validation in SharePoint 2010
• How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation

Also, if you would like to learn more about these techniques, with live demonstration and discussion, I’ll be speaking on Column Validation at July. 2012 meeting of the Federal SharePoint User’s Group (FEDSPUG) in Washington, DC. Come on out, I would love to meet you!

The Problem

A reader asked, “I have form that does not require the ‘phone’ to be filled in. However I want to validate the entry if/when it is filled in. Is there a way to add string at end of the phone validation formula to accept a ‘null’ value to avoid validation error?” Yes, this is possible, and in this post I’ll show you how to do it.

In a nutshell, this is an ‘OR’ type of validation that I discussed in my post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation, with one of the options being no answer at all. In this post, I’ll look at a slightly different way of doing this than the exact way I did it in that post. The are multiple formulas that may work for many of these complex validation requirements, so there isn’t one single correct answer.

A Simple Non-Required Validation Test

Because some of the formulas for Column Validation can be quite complex, I like to set up a “proof of concept” type validation that is more simple first, then I can work on the more complex details.

So, I’ll first test the concept of validating something only if it exists. Then, once I have that working, I’ll integrate the formula I’ve already written for validating a phone number.

I’ve created a list with a column named “phone”. I’ve chose the “Single line of text” data type, set it to a maximum of 12-characters (three digits, a dash, 3 digits, a dash, and four digits), and made it not required.

Since I only want my column validation to run if the user enters something in the field, that is to say, if the field’s value isn’t NULL, my first thought was to use the ISNULL() function and then wrap it in the  NOT() function. (Note: Microsoft has a section with information about available Formulas and Functions in SharePoint 2010.) This method should work fine on Number columns, however, a column of data type Single line of text isn’t NULL if it’s empty, it just has a string of no length. This moved me back in the direction of using nested IF() functions like I did in my post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation.

First, we’ll write a simple formula to only allow a blank phone number. This formula will look like this: =IF(LEN([Phone])=0,TRUE,FALSE) With this formula in place, if there is anything in the Phone field, it won’t validate. However, it validates fine if the field is empty.

Next, we’ll put a simple validation place of the FALSE result. Let’s try to only allow the letter “A” there. So a user can leave it blank, or they can enter a single letter “A”, but nothing else will be allowed. That formula will look like this: =IF(LEN([Phone])=0,TRUE,(IF([Phone]=”A”,TRUE,FALSE))

With this formula, only empty values, or values of a single letter “A” are allowed:

Validating a Phone Number, But Only If One Is Entered

Now that we have the general logic worked out, we need to replace that second condition ([Phone]=”A”) in the formula with the Phone number validation formula. You’ll remember from the post on How to Validate Strict Text Formats in SharePoint 2010 to validate for ###-###-####, the formula is:
=(LEN([Phone])=12)
+(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=23

To make it easier to read, let’s separate the original formula so the parts are on separate lines. It looks like this:
=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF([Phone]=”A”
      ,TRUE
      ,FALSE
    )
)

If we replace the second condition with the Phone Number Validation Condition, it looks like this:

=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF(
(LEN([Phone])=12)
+(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=23
      ,TRUE
      ,FALSE
    )
)

Although this does correctly validate and only allow phone numbers in the ###-###-#### format, it doesn’t display our nice friendly error message when a user enters in invalid information. It displays:

instead of:

As I discussed in the post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation, this is because the formula is trying to evaluate characters that don’t exist. Let’s change our formula to only check the individual characters if it is 12-characters long. With this extra condition, our stub formula will look like this:

=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF(LEN([Phone])=12
      ,TRUE
      ,FALSE
    )
)

This stub formula will work and only allow blank phone numbers or phone numbers that are 12-digits long. To add the testing for the proper characters, we’ll need to wrap the phone number validation formula in an IF() statement and replace the second TRUE result with it. Because this second statement will only run if it’s 12-characters long, we don’t need to include that part of the formula and the sum will be 22 instead of 23. Our formula will look like this:

=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF(LEN([Phone])=12
      ,IF((
(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=22
),TRUE,FALSE)
      ,FALSE
    )
)

Whew! That was a lot of work, but we did it. With that formula, our list doesn’t require a phone number to be entered, but if one is entered, it must be in the proper format.

I’m really enjoying working through these column validation questions and issues and look forward to hearing what others need to validate. Of course, hopefully, the series has been written so that you’ve learned to do this yourself and you’ll be sharing your formulas in the comments instead of asking for new ones.

Happy validating!

I didn’t originally set out to write a series of posts on Column Validation in SharePoint 2010, but I keep thinking of new challenges each time I do. I have no idea how long this series may go (in fact, this may be the last one), but this is the third in the series. Here are links to the previous two posts:

1. How to Validate Strict Text Formats in SharePoint 2010
2. How to Validate an Email Address Using Column Validation in SharePoint 2010

In today’s post we’ll look at how we might allow users to enter data in a one of a couple different formats. The example that comes to mind is US Postal Service ZIP Code validation. Two ZIP Code formats are allowed: the old 5-digit format and the ZIP+4 format that we started using the 1980’s (5-digits, followed by a dash, followed by four more digits).

Using what you learned in my first post of the series you can probably easily write a formula to validate each one of those. Instead of walking through each step of writing those, I’ll just write them for you here:

ZIP Code Validation Formula:

=(LEN([ZIP Code])=5)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
=11

ZIP+4 Validation Formula:

=(LEN([ZIP Code])=10)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
+(MID([ZIP Code],6,1)="-")
+(CODE(MID([ZIP Code],7,1))>47)
+(CODE(MID([ZIP Code],7,1))<58)
+(CODE(MID([ZIP Code],8,1))>47)
+(CODE(MID([ZIP Code],8,1))<58)
+(CODE(MID([ZIP Code],9,1))>47)
+(CODE(MID([ZIP Code],9,1))<58)
+(CODE(MID([ZIP Code],10,1))>47)
+(CODE(MID([ZIP Code],10,1))<58)
=20

Set Up a Sandbox List for Experimenting

Since I’m using these blog posts to teach how to do column validation formatting instead of just giving you the formulas without much explanation, let’s create a sandbox list we can play in and see what is going on. We’ll need 3 columns in the list: a Single line of text for the ZIP Code, a calculated field that uses the first formula for the ZIP Formula, and another calculated field that uses the ZIP+4 Formula. I’ve set that up here and entered a couple examples:

Because those formulas are so long, we’ll write our initial formula using the ZIP Formula column and ZIP=4 Formula column. Then we’ll replace those with the full formulas.

The Logic – Introducing the Undocumented OR() Formula

Let’s create a new calculated column in our sandbox list and call it Combined Formula. Although I found information about the AND() formula, I didn’t see any documentation about an OR() function. I decided to try it and see if it would work anyway. It did! At least, part way. Enter in a formula of =OR([ZIP Formula],[ZIP+4 Formula]). You’ll notice you get the following results:

Dealing with Errors

See those #VALUE! Errors? Ultimately, we know those are No’s, but SharePoint sees them as errors. When a validation formula encounters an error it will give the user an error instead of the message you want. Here is a screenshot showing what the user sees when a validation formula encounters an unchecked error.

What we wanted them to see was this:

We can handle this with the ISERROR() formula we used in yesterday’s post. We’ll also combine it with the IF() function I mentioned briefly in the first post. The IF() function accepts three comma separated parameters: the item to test, the result if the test is true, and the result if the test is false.

To see this in action, let’s work with one column at a time in our Combined Column. Change the formula for this column to =IF(ISERROR([ZIP Formula]),FALSE,IF([ZIP Formula],TRUE,FALSE)). What this does is test for an error first. If it encounters an error then it sets the field to FALSE, if it doesn’t encounter an error, then it checks to see if the formula evaluates to True or False and sets the value accordingly.

This gives us the accurate Yes’s and No’s about the ZIP Formula, without the errors:

Now that we have that working with the ZIP Formula column, let’s modify it to use the ZIP+4 Formula column. That’s as simple as changing the column names: =IF(ISERROR([ZIP+4 Formula]),FALSE,IF([ZIP+4 Formula],TRUE,FALSE)).

Notice this also correctly tells us that the last test is the only one that fits our ZIP+4 criteria:

Back to the Logic

Now that we have the errors all worked out, let’s use the OR() function to see if either of them are valid. Here is what the formula will look like: =OR((IF(ISERROR([ZIP Formula]),FALSE,IF([ZIP Formula],TRUE,FALSE))),(IF(ISERROR([ZIP+4 Formula]),FALSE,IF([ZIP+4 Formula],TRUE,FALSE)))). Notice that this shows us that either of the last two entries are valid, but neither of the first two are:

Putting It All Together

Chances are pretty good that you don’t want to add two extra columns to your SharePoint list every time you have a ZIP Code column for validation purposes. So we’ll need to put our original formulas in the place of the column names we used in our last formula. If you’re thinking, “This is going to be one really long formula!” then you’re absolutely correct!

To make things easier, let’s take that last formula and break it out on different lines and add some indentation so it’s easier to read:

=OR(
  (
    IF(ISERROR(
        [ZIP Formula]
      )
      ,FALSE
      ,IF(
        [ZIP Formula]
        ,TRUE
        ,FALSE
       )
      ))
    ,(IF(ISERROR(
        [ZIP+4 Formula]
      )
      ,FALSE
      ,IF(
         [ZIP+4 Formula]
         ,TRUE
         ,FALSE
    ))
  )
)

Now that you have that, simple replace both instances of [ZIP Formula] with the first formula from the beginning of this post and both instances of the [ZIP=4 Formula] with the second formula (remember to drop the equals sign at the beginning and wrap it in parenthesis). This will give you a completed formula that looks like this:

=OR(
  (
    IF(ISERROR(
        ((LEN([ZIP Code])=5)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        =11)
      )
      ,FALSE
      ,IF(
        ((LEN([ZIP Code])=5)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        =11)
        ,TRUE
        ,FALSE
       )
      ))
    ,(IF(ISERROR(
        ((LEN([ZIP Code])=10)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        +(MID([ZIP Code],6,1)="-")
        +(CODE(MID([ZIP Code],7,1))>47)
        +(CODE(MID([ZIP Code],7,1))<58)
        +(CODE(MID([ZIP Code],8,1))>47)
        +(CODE(MID([ZIP Code],8,1))<58)
        +(CODE(MID([ZIP Code],9,1))>47)
        +(CODE(MID([ZIP Code],9,1))<58)
        +(CODE(MID([ZIP Code],10,1))>47)
        +(CODE(MID([ZIP Code],10,1))<58)
        =20)
      )
      ,FALSE
      ,IF(
        ((LEN([ZIP Code])=10)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        +(MID([ZIP Code],6,1)="-")
        +(CODE(MID([ZIP Code],7,1))>47)
        +(CODE(MID([ZIP Code],7,1))<58)
        +(CODE(MID([ZIP Code],8,1))>47)
        +(CODE(MID([ZIP Code],8,1))<58)
        +(CODE(MID([ZIP Code],9,1))>47)
        +(CODE(MID([ZIP Code],9,1))<58)
        +(CODE(MID([ZIP Code],10,1))>47)
        +(CODE(MID([ZIP Code],10,1))<58)
        =20)
         ,TRUE
         ,FALSE
    ))
  )
)

Whew! Now that’s what I call validation!

Before we add this to our validation field, let’s test the combined formula in our Combined Formula calculated column. As you can see, this gives us the same result we got before:

1024 Character Limit for Column Validation Formulas

If you paste this formula into the Column Validation field on the ZIP Code column, you’ll notice that it trims it about half-way. Microsoft is enforcing a 1,024-character limit on Column Validation formulas and this formula has 2,815-characters with the indented spacing. If we remove all the extra spaces, that brings it down to 2,135-characters, bit that’s still 1,111 too many. If we change our column name from ZIP Code to simply ZIP, we can save 7-characters each time we reference it (we don’t have to use the square brackets for single word column names). This brings us down to 1,704-characters. We still need to eliminate 680 characters. Can we do it?

The first formula is now 279-characters and the second formula is now 511-characters. If we can reference each formula only once, that will save 790-characters and get us under our limit. Hmmm…

Rephrasing the Solution

As it turns out, there is a way to only reference each formula one time. We only need to use the ZIP formula if the user entered 5-characters and we only need to use the ZIP+4 formula if the user entered 10-characters. Anything except 5- or 10-characters is not valid, so we don’t need to test that. This also means that we don’t need to do any error checking.

So, let’s begin with an IF() statement this time that just tests to see if the input is 5-characters long: =IF(LEN(ZIP)=5,TRUE,FALSE).

Then we’ll add in the test for 10-characters long if it fails the 5-character test: =IF(LEN(ZIP)=5,TRUE,(IF(LEN(ZIP)=10,TRUE,FALSE))).

Let’s write this with some indentation so we can see what we’re working with. We can always replace the spaces if we need more characters.

=IF(
  LEN(ZIP)=5
    ,TRUE
    ,(IF(
      LEN(ZIP)=10
        ,TRUE
        ,FALSE
    ))
)

Now, all we need to do is replace the TRUE portions of this formula with the tests we want to run. We can also exclude the length test now too. So, here is what the formula looks like:

=IF(
  LEN(ZIP)=5
    ,( (CODE(MID(ZIP,1,1))>47)
      +(CODE(MID(ZIP,1,1))<58)
      +(CODE(MID(ZIP,2,1))>47)
      +(CODE(MID(ZIP,2,1))<58)
      +(CODE(MID(ZIP,3,1))>47)
      +(CODE(MID(ZIP,3,1))<58)
      +(CODE(MID(ZIP,4,1))>47)
      +(CODE(MID(ZIP,4,1))<58)
      +(CODE(MID(ZIP,5,1))>47)
      +(CODE(MID(ZIP,5,1))<58)
      =10)
    ,(IF(
      LEN(ZIP)=10
        ,((CODE(MID(ZIP,1,1))>47)
        +(CODE(MID(ZIP,1,1))<58)
        +(CODE(MID(ZIP,2,1))>47)
        +(CODE(MID(ZIP,2,1))<58)
        +(CODE(MID(ZIP,3,1))>47)
        +(CODE(MID(ZIP,3,1))<58)
        +(CODE(MID(ZIP,4,1))>47)
        +(CODE(MID(ZIP,4,1))<58)
        +(CODE(MID(ZIP,5,1))>47)
        +(CODE(MID(ZIP,5,1))<58)
        +(MID(ZIP,6,1)="-")
        +(CODE(MID(ZIP,7,1))>47)
        +(CODE(MID(ZIP,7,1))<58)
        +(CODE(MID(ZIP,8,1))>47)
        +(CODE(MID(ZIP,8,1))<58)
        +(CODE(MID(ZIP,9,1))>47)
        +(CODE(MID(ZIP,9,1))<58)
        +(CODE(MID(ZIP,10,1))>47)
        +(CODE(MID(ZIP,10,1))<58)
        =19)
        ,FALSE
    ))
)

That’s 1088-characters with the spaces for indentation—816 if we remove all the spaces. We did it! In fact, it could probably made even smaller by checking the first five characters separately from the last 5. I’ll leave that as a homework exercise for you though.

Implementing the Solution

Now let’s try out the formula, add a friendly validation message, and try to enter some valid and invalid Zip Codes.

Notice that anything that doesn’t meet our strict criteria displays our friendly error message:

What Have We Learned?

I’ve been all over the place in this post, but I think you’ve learned a lot. Let me summarize:

• It is possible to have multiple options that are valid (‘OR’ type options).
• This can be accomplished through IF() functions and/or the OR() function.
• Validation formulas that result in an error, rather than TRUE or FALSE, will show an unfriendly error message instead of the friendly error the user entered.
• ISERROR() can be used to trap errors if they may be encountered.
• There is a 1024-character limit to Column Validation formulas.
• Columns with a single name don’t need square brackets around them in formulas.
• The most obvious formulas can often be shortened by looking at the problem a different way.

Is there anything I haven’t touched on in these three posts? What other kinds of validation have you not been able to do? Let me know in the comments!

We often need to ensure that users enter text information in a specific way. For example, perhaps our company uses part numbers such as TL-1400 and RW-1555. Every part number begins with two letters, followed by a dash, followed by four digits. Or perhaps we use Social Security Numbers in SharePoint (if you do, I’m sure you have the appropriate security in place) and need to insure they always follow the same format of three digits, a dash, two digits, a dash, and four digits. We may want to make sure that phone numbers always follow the format of three digits, a dash, three digits, a dash, and four digits. We may want to make sure that an email address field contains some characters, an ampersand, some more characters, a dot, and some more characters (as well as doesn’t include spaces and other special characters).

In this post, I’m going to explain how to validate these types of information and put you on the path to validating your own text formatting in your organization. This is a great method to use when creating a Site Column for frequently used information that needs strict formatting.

Several people have written about how to use SharePoint Designer to modify forms to do this type of validation but that is only a solution for a single instance of usage. If you’re running Enterprise Edition, you can modify your list forms in InfoPath and do formatting validation there. However, once again, it only solves the problem for that particular list.

Both the SharePoint Designer and InfoPath solutions use Regular Expressions. This is a very easy way to validate information (although it’s not so easy to learn). Although SharePoint 2010 does allow Column Validation, Regular Expressions are not supported. However, there are lots of string functions we can use to meet our needs!  Click here to access information about the formulas that are available in SharePoint 2010 and how to use them.

Validate a Social Security Number in SharePoint 2010

Let’s begin by seeing how we might validate a Social Security Number in SharePoint 2010. I’ve created a custom list and added a column of data type Single line of text named SSN.

Our requirements are that the field must be 11 characters long—consisting of three digits, a dash, two digits, a dash, and four digits. Let’s edit the column and create a formula to check only the number of characters. We’ll use the formula =Len([SSN])=11and also set our User message we want people to see when we have everything working to, “Must be in the format ###-##-####!“

If we test this by trying to add a list item that has only 10 characters, we see our error message.

So, we have the first piece of criteria we need. We’ll remember that and work on another piece. Let’s validate that the fourth character is a dash. To do this, we’ll use the MID function like so: =MID([SSN],4,1=”-“). This tells SharePoint to extract a string out of the MIDdle of our field that is 1 character long and begins with the character in the 4th position, then check to see if it is a dash.

If we try to enter any value that doesn’t have a dash as the fourth character, we see the error.

However, it accepts anything that has a dash in that position.

The next thing we want to do is combine these two formulas. You would think we could use the AND operator like so: =LEN([SSN])=11 AND (MID(SSN,4,1)="-"). However SharePOint doesn’t like that and will tell us “The formula contains a syntax error or is not supported.” Ugh! We could use the IF function, but it creates a mess that looks like this: =IF(LEN([SSN])=11,(IF(MID([SSN],4,1)="-",TRUE,FALSE)),FALSE). Yuch! In fact, double Yuch! If you think that’s difficult to read (not to mention write and debug) with only two pieces of criteria, wait until we have 21 pieces of criteria! That’s just not usable at all. So, is there anything else we can do? Yes there is!

A TRUE value equates to the number 1 while a FALSE value equates to 0. We can use this fact to create a formula that is easier to read, like so:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
=2

This tells SharePoint to take the result of the first condition, add it to the result of the second condition and see if the combined result is 2. If it is, our formula will evaluate to TRUE.

With this formula, both pieces of criteria must be met for SharePoint to allow the entry:

Now that we have a simple way to check for multiple conditions, we can add a check for the second dash in the seventh position:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
=3

The next thing we want to do is make sure that the first character is a digit. To do this, we’ll look at it’s ASCII value. Click here for a chart you can use to look up ASCII values. The number 0 has an ASCII value of 48 and the number 9 has an ASCII value of 57. So we can do two checks to see if the value is greater than 47 and less than 58. We’ll use the CODE function to get the value. If we add these two tests to our function, it will look like this:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
+(CODE(MID([SSN],1,1))>47)
+(CODE(MID([SSN],1,1))<58)
=5

Now, our field must be eleven characters long, have a dash as the fourth character, have a dash as the seventh character, and must begin with a number.

Now you know everything you need to know to finish the formula to have it check for digits in the other eight positions. Here is the finished formula:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
+(CODE(MID([SSN],1,1))>47)
+(CODE(MID([SSN],1,1))<58)
+(CODE(MID([SSN],2,1))>47)
+(CODE(MID([SSN],2,1))<58)
+(CODE(MID([SSN],3,1))>47)
+(CODE(MID([SSN],3,1))<58)
+(CODE(MID([SSN],5,1))>47)
+(CODE(MID([SSN],5,1))<58)
+(CODE(MID([SSN],5,1))>47)
+(CODE(MID([SSN],5,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
=21

Validate a Telephone Number in SharePoint 2010

We can use these these same techniques to validate a phone number. Here is a formula to validate for ###-###-#### format:
=(LEN([Phone])=12)
+(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=23

And here is a formula to validate for (###)###-#### format:
=(LEN([Phone])=13)
+(MID([Phone],1,1)="(")
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(CODE(MID([Phone],4,1))>47)
+(CODE(MID([Phone],4,1))<58)
+(MID([Phone],5,1)=")")
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(CODE(MID([Phone],8,1))>47)
+(CODE(MID([Phone],8,1))<58)
+(MID([Phone],9,1)="-")
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
+(CODE(MID([Phone],13,1))>47)
+(CODE(MID([Phone],13,1))<58)
=24

Validate a Part Number in SharePoint 2010

In my opening paragraph I mentioned the possibility of a part number in the format of two letters, a dash, and four numbers such as TL-1400 or RW-1555. We could validate this with:
=(LEN([PartNum])=7)
+(CODE(MID([PartNum],2,1))>64)
+(CODE(MID([PartNum],2,1))<90)
+(CODE(MID([PartNum],3,1))>64)
+(CODE(MID([PartNum],3,1))<90)
+(MID([PartNum],4,1)="-")
+(CODE(MID([PartNum],5,1))>47)
+(CODE(MID([PartNum],5,1))<58)
+(CODE(MID([PartNum],6,1))>47)
+(CODE(MID([PartNum],6,1))<58)
+(CODE(MID([PartNum],7,1))>47)
+(CODE(MID([PartNum],7,1))<58)
+(CODE(MID([PartNum],8,1))>47)
+(CODE(MID([PartNum],8,1))<58)
=14

More…

I hope this has been helpful. Feel free to leave helpful formulas you’ve developed in the comments!