Excel Character Question Mark In A Box

[themusicbox]

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!!

[Richard Schollar]

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.

[themusicbox]

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

[Richard Schollar]

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).

[themusicbox]

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

[themusicbox]

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





[Richard Schollar]

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

[themusicbox]

No - first thing I tried!

[Richard Schollar]

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.

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

[themusicbox]

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

Thanks for your help, much appreciated.

Steve