Excel non-display characters

Factual Questions
1

I have an Excel file that contains non-display characters. These appear as little boxes, a bit like this: 9083 Foothills Blvd. [] Ste. 390 Note that when I pasted that here, the suite number appeared on a new line. Normally I’d do a Find & Replace on ALT+010. I’m telecommuting today, so I’m on a Mac and can’t do that. (I’m connected to my office PC by RDC, by the way.) I sent instructions to a coworker how to get rid of the CRLF, and that took care of some of them. But there are still little boxes in the data, so they must not be ALT+010. I’ve seen this in another file too. Fortunately, that file is .txt and I can get rid of the offending non-display characters by uploading it into Access. In this case, I can’t upload into Access until I get rid of the non-display characters.

Is there a way to find out the ALT code for the characters that are not ALT+010?

2

You mean like, =CODE(B1)?

3

How do I use that?

4

There is the CODE function in excel which will show the ASCII code for the first character of a referenced cell. As Inigo said.

It’s probably a carriage return character (ASCII 13). They usually hang around with the 10’s in the DOS world.

5

The characters are never in the fist position. I’ll try ALT+013 when I go into the office.

6

You can select substrings with the MID function. The following returns the ASCII code of the third character of the contents of cell D4:

=CODE(MID(D4,3,1))

7

If you want it do go away can’t you just use the Edit, Replace option, and replace it nothing?

8

If when you paste that into Excel, you use Paste Special and choose unformatted text, that should prevent this problem.

9

Yes, I replace non-display characters with a space.

Only I have to know what to replace.

10

I haven’t been able to import anything weird into Excel to test it but what I was suggesting is copying the offending item and pasting that into the replace function. And I wouldn’t replace it with a space, I would leave it blank (unless you want to insert a space). And I second the notion that you use paste special to avoid importing odd coding. If it’s done through a query then copy and paste the query using paste special.

11

I tried copying and pasting the , but it doesn’t work in Excel. (I get another file with non-display characters that comes as text. I can C&P there; only for that one I can import it into Access and skip the unneeded field that contains the character.) The thing that gets me is that it looks and acts like a CRLF ALT+010, but it isn’t.

12

I tried copying some of the smiley characters into Excel. From there I couldn’t copy into “Replace” so I created a floating text box, copied the smiley into the text box and then copied it from there into “Replace.”

Give that a try.