FilterPoint - FAQ - Ampersand in Filter

The SharePoint interface that FilterPoint uses cannot accept filters containing ampersands characters (&).

If you try to use a filter containing an ampersand FilterPoint will change it to an dash (-) and display a warning.

A workaround for this problem is to use a calculated column to replace all instances of ampersand in the field that you want to filter on with another character such as underscore (-).

For example in the following list we have an ampersands in the Title column.

1) We can create a new calculated column called "Clean Title" (or any name that is appropriate) and use the following formula to replace the & with -

=IF(ISNUMBER(FIND("&",[Title])),REPLACE([Title],FIND("&",[Title]),1,"-"),[Title])

Note - if be sure to replace the two [Title] references above with the field that you're wanting to filter on.

2) Change FilterPoint to filter on this field, instead of the original field.

Multiple Ampersands (&)

This will only change the first & that it finds though - unfortunately SharePoint doesn't provide a way to change ALL occurrences of one letter with another so if you have strings with 2 ampersands you would have to add two extra columns using the method above. The first "Clean Title" would clean the first ampersand from Title, and the second "Clean Title 2" would replace the second ampersand from "Clean Title". You would then change FilterPoint to filter on thie "Clean Title 2" field.

Obviously this could quickly get out of had if you have data with 3, 4 or more & in the field - in which case you may want to consider removing the ampersands manually from the field itself rather than using a calculated column to produce a clean copy.