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.

 

[ayoungs]

Yeah I've been messing around with that. It seems to be functioning, but not properly. I will keep messing around to see if I can figure it out. Thanks.

 

[ayoungs]

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.

It's working. Thanks for the tip about unicode characters.

 

[TekFin2008]

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)

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.

Hi
I was about to create a new thread but thankfully I found this thread with almost same issue I have


my problem is that when I copy paste data from a text based output of an oracle report into excel. it creates special characters like question mark in a box or a blank square in the beginning of the line or the symbol ♀ (Alt+12). this basically messes up my text to column function because those random rows affect the uniformity of the data within the rows.

I cant find and replace those special characters I cant copy them and paste them into find function either,.

it will be something like this, 3 lines ok and fourth one will have this issue and this will repeat itself at random intervals and if the rows are too many then its very cumbersome and annoying because I cant devise an automated tool to convert data through text to columns etc.

04-OCT-15 BSAUSB3_STANDAR USB3 bundle co

04-OCT-15 BSAUSB3_STANDAR USB3 bundle co

04-OCT-15 BSAUSB3_STANDAR USB3 bundle co

♀ 04-OCT-15 BSAUSB3_STANDAR USB3 bundle co

<tbody>
</tbody>

please help in some way to find and replace those boxes at the beginning of the row. not sure if they represent a page break in the text file from where I copied the data because there is nothing there in the text files.
is there any way to teach excel macro to look up ASCII characters like ♀?

problem is that =code() returns 63 for them instead of 12