Functions return #FILTER? Error When Filter Contains Single Quote
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.)
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.
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.
"[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,"'","''") & "'"