How to Create a SharePoint “Does Not Begin With” Filtered List View

Date:September 9th, 2011 Author: Tags: , , ,
Category: Calculated Columns, Filter Comments:6 ;

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.

Tags: , , ,

6 Responses to “How to Create a SharePoint “Does Not Begin With” Filtered List View”

  1. Sean says:

    I can get the “Yes” where it returns an error ( “does not contain”) and “No” where “USA” is found. But I can’t get the filter to work…Help!!

    Office365 on the web

  2. Ryan says:

    @Sean – Just starting up my mind reading app… hold on… hmmm… oh, doesn’t seem to be working at the moment 😉

    Give me a few details about how its not working (e.g. is it showing everything, nothing, what filter have you put in etc) and I’ll see what I can do.

  3. Thiru says:

    Nice one !
    Thanks !

  4. Rich Montoya says:

    Just what I was looking for. Thank you!

  5. John says:

    Worked like a charm!!

Leave a Reply

Anti-Spam Quiz:

We need to confirm your email address so we have just sent you an email.


Please click the link in the email and you will have immediate access to the download