3305 Main Street Ste 019 Vancouver, WA 98663

Functions return #FILTER? Error When Filter Contains Single Quote

Background

When using the TSFIND, TSSUM, or TSCOUNT function with a filter containing a single quote in one of the values, an error message like one of the following is returned by the function:

  • #FILTER? ("[VNAME] LIKE 'My'Value'" is not valid.)
  • #FILTER? ("[VNAME] = 'My'Value'" is not valid.)
  • #FILTER? ("[VNAME] <> 'My'Value'" is not valid.)

Cause

The single quote character is used to mark the beginning and ending of a literal text value. If a single quote is used in the middle of a literal text value, it appears to the query as if it is marking the end of the value. The characters that follow are then considered to be invalid because they don't constitute a valid filter expression.

Resolution

You can replace the single quote with two consecutive single quotes. When the query sees two consecutive single quotes, it will treat them as one single quote.

Example

"[VNAME] = 'My''Value'"

If the value you are using is a cell reference, your filter might look something like this:

"[VNAME] = '" & $B$5 & "'"

In this case, you can use Excel's SUBSTITUTE function to replace all instances of a single quote with two single quotes like this:

"[VNAME] = '" & SUBSTITUTE($B$5,"'","''") & "'"