Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Excel Character Question Mark In A Box

This is a discussion on Excel Character Question Mark In A Box within the Excel Questions forums, part of the Question Forums category; Hi I've searched for this in the forums but can't find anything. I need to find/replace a character in Excel, ...

  1. #1
    New Member
    Join Date
    May 2008
    Posts
    6

    Default Excel Character Question Mark In A Box

    Hi I've searched for this in the forums but can't find anything. I need to find/replace a character in Excel, it's a question mark in a box and looks like this

    I've tried searching for special characters (using the ALT key and number pad) but I can't find anything matching this.

    When I save the spreadsheet as a CSV (TAB delimited) this character gets picked up as a TAB, but when I try find replace on the TAB character it doesn't work!

    I'm using Excel 2007.

    Please help!!

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,661

    Default Re: Excel Character Question Mark In A Box

    Hi & Welcome to the Board!

    ou can use the CODE function to determine what the code number of the character is eg:

    =CODE(MID(A1,1,1))

    assuming the character is in a string in A1 (adjust Mid settings to suit). If the character is on its own, you can omit the Mid.

    This assumes that it is from the default character set (ie 0 which is the setting for all Western (ANSI) settings). I suspect it is a specific font giving rise to the character.

    See what the CODE returns and report back.
    Richard Schollar

    Using xl2013

  3. #3
    New Member
    Join Date
    May 2008
    Posts
    6

    Default Re: Excel Character Question Mark In A Box

    It comes up with the number 13 using that formula. Thanks for the quick reply!

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,661

    Default Re: Excel Character Question Mark In A Box

    OK that suggests it is a Carriage Return. You can do a find/replace on Carriage Returns (ie in the Find box of the Find/Replace dialog, hold down the Alt key and on the numeric keypad type 013 - this will enter a Carriage Return which you can then replace with nothing - i effectively removing the CR).
    Richard Schollar

    Using xl2013

  5. #5
    New Member
    Join Date
    May 2008
    Posts
    6

    Default Re: Excel Character Question Mark In A Box

    Hi I tried ALT 013 and ALT 010 but neither work. It's a very strange problem!

  6. #6
    New Member
    Join Date
    May 2008
    Posts
    6

    Default Re: Excel Character Question Mark In A Box

    I forgot to mention that the character is coming up between to paragraph tags (it's HTML) so it looks like



    Code:
     
    


  7. #7
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,661

    Default Re: Excel Character Question Mark In A Box

    If you just copy the character and paste it into the Replace dilog does it work then?
    Richard Schollar

    Using xl2013

  8. #8
    New Member
    Join Date
    May 2008
    Posts
    6

    Default Re: Excel Character Question Mark In A Box

    No - first thing I tried!

  9. #9
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,661

    Default Re: Excel Character Question Mark In A Box

    Plonk the following macro in a standard module and select a range of cells that includes this funny character and run the macro - if the character is from a non-standard character set then it will highlight the cell in red.

    Code:
    Sub IdentifyCharacters()
    Dim r As Range, b() As Byte, i As Long, cell As Range, strText As String
    Set r = Selection
    For Each cell In r
        If Not IsEmpty(cell.Value) Then
            strText = cell.Value
            b = strText
            For i = LBound(b) + 1 To UBound(b) Step 2
                If b(i) > 0 Then
                    cell.Interior.ColorIndex = 3
                    Exit For
                End If
            Next
        End If
    Next cell
    End Sub
    Richard Schollar

    Using xl2013

  10. #10
    New Member
    Join Date
    May 2008
    Posts
    6

    Default Re: Excel Character Question Mark In A Box

    All I needed in the end was the =CLEAN() formula! This takes out any non-printing characters.

    Thanks for your help, much appreciated.

    Steve

Page 1 of 2 12 LastLast

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
  •  


DMCA.com