Can't remove question mark in box symbol

[Infosec]

I have a workbook where some cells have a small question mark in a box followed by text as follows;

?SMITH,JOHN JAMES

the ? actually appears inside a box. I need to remove the box with the question mark while keeping the text.

When I use =CODE the character number is 12. When I go to Find/Replace press the ALT key and type 12 in the "Find" box, what appears is the symbol for female not the question mark in a box. I clicked on "Replace" anyway but got a pop-up box that Excel cannot find a match. Next I tried pressing the ALT key and typing 012 in the "Find" box, but when I release the ALT key nothing appears in the "Find" box.

I discovered the ASCII code for a question mark is 63, so I pressed the ALT key and typed 63 in the "Find" box. I didn't type anything in the "Replace" box because I just want to remove the question mark box. When I clicked on the Replace button, everything in the cell was deleted. I tried again only this time I put a semicolon in the "Replace" field. When I clicked on the Replace button, all text in the cell was replaced with a semicolon. So all I've managed to do is either remove all text in the cell or replace all of it with another character.

I also tried the =SUBSTITUTE formula but that creates another wrinkle. When I enter the specific cell # in the Text field, the character displayed is just an empty box with no question mark in it. If I could find the ASCII code that matches, I still don't know what to put in the Instance_num field so that only the box is removed. No matter what I put in there I keep getting a circular formula error.

Does anyone have any suggestions for me to try?

 

[poolhall]

try =CLEAN() formula.

 

[Infosec]

Tried that but it didn't work. The formula result in the "Function Arguments" window is exactly what I want it to be just the text with no box. Unfortunately, when I click on OK, I get the Circular Reference Warning and the value in the cell changes to a zero.

 

[poolhall]

how come? let's say your cell with the symbol you want to get rid of is A1. Your formula in B1 will be =CLEAN(A1). Then you can copy down the formula, if needed. Then copy cells with formulas and paste values only.

 

[Infosec]

OMG, it worked!! Thank you so much! I didn't understand how to apply the formula (insert new column, enter formula, paste value only back into original cells) until I read your post. I was missing the forest for the trees. Thanks again!

 

[poolhall]

you're welcome

 

[tcnt9176]

Instead of clearing that box with the question mark. I need to replace it with a recognizable character that I can then deliminate. Any ideas???

 

[poolhall]

question mark in the box is some unprintable character. It may be ASCII symbols with code 10, 12 or 13 most commonly. To replace it, you could use the SUBSTITUTE function, for example:

=SUBSTITUTE(A1,CHAR(10),"|")

 

[poolhall]

question mark in the box is an unprintable character. It may be an ASCII symbol with code 10, 12 or 13 most commonly. To replace it, you could use the SUBSTITUTE function, for example:

=SUBSTITUTE(A1,CHAR(10),"|")

 

[Sesom0000]

in the find/replace box type "~?" to find or replace a "?"