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:

AW 1713-T1

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:

AW 1713-T2

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


   i = 1

   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

     End If



   StripChars = strTestString



   Exit Function



   MsgBox "Error No: " & Err.Number & "; Description: " & _


   Resume ErrorHandlerExit


End Function

Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.