Results 1 to 2 of 2

Thread: Filter for rows containing "black diamond" question mark
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filter for rows containing "black diamond" question mark

    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?
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter for rows containing "black diamond" question mark

    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
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •