Apostrophes in Text strings used for filtering
Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
If you need to filter by a Text value, such as person’s name or a company name, you may run into this problem:
An error caused by an apostrophe in a Text string
This is because the apostrophe is a Text delimiter. The way I deal with this problem is to make a calculated field in which the problem field is stripped of characters that can cause problems in Text strings, and do the filtering on that field.
For example, the TrimmedTechnician field strips out problem characters from the Techician field:
A calculated field to strip out problem characters
The StripChars procedure is listed below; the list of bad characters can be edited as wished:
Public Function StripChars(strText As String) As String
'Strips a spaces and parentheses from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Last modified by Helen Feddema 18-Jun-2016
On Error GoTo ErrorHandler
Dim strTestString As String
Dim strTestChar As String
Dim lngFound As Long
Dim i As Integer
Dim strStripChars As String
strStripChars = " ()-'/\"
strTestString = strText
Do While i <= Len(strTestString)
'Find a strippable character
strTestChar = Mid$(strTestString, i, 1)
lngFound = InStr(strStripChars, strTestChar)
If lngFound > 0 Then
strTestString = Left(strTestString, i - 1) & Mid(strTestString, i + 1)
i = i + 1
StripChars = strTestString
MsgBox "Error No: " & Err.Number & "; Description: " & _
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.