Filter for rows containing "black diamond" question mark

[JugglerJAF]

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?

 

[JugglerJAF]

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:

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

 

[gothic chicken]

As always, even old questions can be helpful!

Try this webpage: How to remove the diamond with question mark symbol

Summary:

To remove the black diamond with white question mark symbol​

Try to use the formula:
= SUBSTITUTE (<cell>,UNICHAR(65533),""):

To remove the [white rectangle] with question mark symbol
Try to use the formula:
= SUBSTITUTE (<cell>,UNICHAR(12),""):