Wonderful though SharePoint is most of the time, occasionally it just lets you down at the most unexpected moment.

So, last week, I wanted to look at a list of all of our sales that were not made to organizations in the USA.

Simple I thought, since all our sale reference numbers start with a 3 letter country code I shall just create a view for all records where Reference Number does not begin with “USA”

So, create a new view, off we go:

Oh dear, we have an option for “begins with” and “contains” but nothing for “does not begin with” or “does not contain”. How disappointing!

So, quick call to Ryan, the king of the calculated column, and we have a handy workaround which seemed worth sharing with you.

Create a calculated column using the following formula which will just display the first 3 letters of the reference code – call it something like “First 3 letters”.

=LEFT([Reference Number],3)

Then create a view with a filter:

WHERE “First 3 Letters” “Is not equal to” “USA”

Easy as that!

You can use this method to create a view for:

• Does not begin with
• Does not end with (using the RIGHT instead of LEFT function)

“Does not contain” is a tad more complicated but can be achieved by using the SEARCH function. So in our example

SEARCH("USA",[Reference #],1)

Will return the first position of USA in an entry in the Reference Number field, or an error if it can’t be found.

So combining this calculated column:

=ISERR(SEARCH("USA",[Reference #],1))

Which gives “Yes” where it returns an error ( “does not contain”) and “No” where “USA” is found, with a filtered view with shows only “Yes” results, will give you records which do not contain USA.

See our Calculated Column Cheat Sheet for these and other useful formulas.

Hope this helps, and if you have any suggestions for other calculated column ninja moves we would love to hear them.