Special Character(Question Mark enclosed within a box)

[ayoungs]

I haven't been able to figure this one out yet. I have searched many forum posts but cannot find the answer. I did not create the spreadsheet I am currently working on so I have formatting from PDFs and the internet jumbled into one. I am working on a vba routine to parse my text fields and format them the way I want. The question mark enclosed in a box has shown up so I tried a variety of Characters (10,12,13). I then pasted this character from B1 to B255 with the following formula next to it:

=SUBSTITUTE(B1,CHAR(ROW(A1)),"999")

This worked on none of the rows. I also printed out all of the characters with =CHAR(ROW(A1)), and the character looks exactly like char(12) on my spreadsheet, but that doesn't work to replace it. Has anyone experienced this? From most forum posts I've seen, it seems that clean would get rid of the character (not what I want because this question mark seems to be in place of a bullet). However, clean does not work on this character which means it must be printable. Other posts said char(13) would work as it was a carriage return, but it did not work for me. Any help is greatly appreciated.

 

[B540Glenn]

Does the font in the output cell match the font from the input?

Char(10) looks like an empty box in Arial but a box with a question mark in Calibri.

 

[ayoungs]

Does the font in the output cell match the font from the input?

Char(10) looks like an empty box in Arial but a box with a question mark in Calibri.

I hadn't thought about that, but it turns out all fonts are Calibri, so I guess that's not the case.

 

[Cindy Ellis]

Try using the CODE function to figure out which character it is. CODE only works for the first character in a cell, but if you've already copied it by itself to B1, then in C1, the following formula SHOULD give you the ASCII code for it:

=CODE(B1)

 

[ayoungs]

I've tried that but it returns 63 for a question mark. I'm not really sure why. It doesn't recognize it as chr(63) when I search for it.

 

[Cindy Ellis]

Have you tried copying the character to the Find/Replace dialog? Sometimes that works when nothing else seems to work. Put the problem character in "Find what", and leave "Replace with" blank.

 

[VoG]

It may be a Unicode character. There is a UDF from pgc01 here http://www.mrexcel.com/forum/showth...Unicode-code-of-a-character&highlight=unicode

 

[ayoungs]

In excel I just tried copying and pasting the symbol into my substitute function and that worked, but when I try doing that into vba, I get '??'. So then I decided to read that cell into VBA. It works but now takes twice as long because it flips between sheets(I have the question mark on a separate ref sheet). It's not a very elegant workaround.

 

[ayoungs]

Thanks. I checked out that post and found it may be unicode 0xDBC0. How can I use this in my vba routine?

 

[VoG]

I think ChrW will help you. See http://www.dailydoseofexcel.com/archives/2010/11/19/unicode-and-vbas-chrw-and-ascw-functions/

Sorry, I have to go now but will check back later.