Filter for rows containing "black diamond" question mark

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
297
Office Version
  1. 365
Platform
  1. Windows
I have a data file containing data from several different countries, many of which use accented characters in their alphabets.

The file has been unable to interpret many of these and replaced them a black diamond with a white question mark inside: �

On my PC (Office Professional 2013), I can select this character, copy it, and use it in the filter or search dialogs to find cells containing this character, but a colleague running Office 365 is having a different experience. The character is displayed in the worksheet as the black diamond, but when she edits the cell (directly, or via the formula bar), it displays as an empty white square (black border) with a question mark inside, and using this character to filter or search doesn't return the rows containing the black diamond character.

I've tried filtering for just a question mark (the character comes out as CODE 63) by using the ~? trick, but this doesn't work on either PC.

I don't know if it's an Office 365 setting or a PC language setting, but how can she filter to show rows containing this weird little character or otherwise identify them?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I've managed to implement a workaround for now by creating a custom function that returns TRUE if the cell contains specified ascii code characters (basically 32 to 126, but excluding 63 which is ?, and other codes for known accented characters). If any unexpected characters are found, then FALSE is returned, and this column can be T/F column used to filter on FALSE to show rows with unexpected characters which includes the annoying black diamond.

It's probably not the most efficient way of doing it, but I had to come up with something quickly before my colleague exploded from excess frustration!

Code, if anyone's interested was as follows:
Code:
Function MYALPHANUM(my_cell As String) As Boolean
    Dim intPos As Integer
    If my_cell = "" Then
        MYALPHANUM = True
    Else
        For intPos = 1 To Len(my_cell)
            Select Case Asc(Mid(my_cell, intPos, 1))
                Case 32 To 62, 64 To 126, 154, 156, 158 To 159, 170, 192 To 214, 216 To 246, 248 To 255
                     '32 to 126 (excluding 63 which is ?) = most common letters, numbers, and punctuation
                     '170 = ª (common on Spanish data)
                     'others = various accented characters
                    MYALPHANUM = True
                Case Else
                    MYALPHANUM = False
                    Exit For
            End Select
        Next
    End If
End Function
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top